How to return Multiple Output Parameter from Stored Procedure in Oracle

You may return multiple output parameters from a stored procedure by using the OUT parameter when creating an Oracle stored procedure. A stored procedure might have a number of OUT parameters. The OUT parameter is used to send the stored procedure’s output data to the PL/SQL code block that calls it from Oracle. If a stored procedure contains multiple OUT parameters, it can return multiple values to the argument variables stated in the calling PL/SQL code block. The returned values will be saved in the PL/SQL local variable that was sent to the stored procedure as an argument.

The multiple OUT parameters defined in the stored procedure will return multiple output parameter values from stored procedure in oracle. Each OUT parameter returns a value from the stored procedure to the calling PL/SQL code.



Stored Procedure with Multiple OUT Parameters

A stored procedure with two OUT parameters and one IN parameter is created in the example below. The IN parameter reads data from the stored procedure from the PL/SQL code block. In the stored procedure, the IN parameter is used as a local variable. The OUT parameter variable is used to assign the values to the PL/SQL code block that must be returned from the stored procedure. If a stored procedure has more than one OUT argument, the stored procedure will return multiple values after execution.

Code

SET serveroutput on;

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2, msg2Out OUT varchar2 )

IS
    msg1 varchar2(100);
    msg2 varchar2(100);
begin
    msg1 := 'Hello '||nameIn;
    msgOut := msg1;

    msg2 := 'Welcome '||nameIn;
    msg2Out := msg2;
    
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('msg1 = ' || msg1);
    dbms_output.put_line('msg2 = ' || msg2);
    dbms_output.put_line('msgOut = ' || msgOut);
    dbms_output.put_line('msg2Out = ' || msg2Out);
end;

Output

Procedure MYPROCEDURE compiled


Returning Multiple Output Parameter from Stored Procedure

The following example will show how to return multiple output parameters from a stored procedure and how to assign the output values to the local variable in the PL/SQL code block. The PL/SQL code should be declared with local variables that is used for OUT arguments. The stored procedure will return the value and assigns to the local variable which is passed as an argument for the OUT parameters.

Code

declare 
    msg1var varchar2(100);
    msg2var varchar2(100);
begin
    myprocedure('yawin', msg1var, msg2var);
    dbms_output.put_line('msg1var = ' || msg1var);
    dbms_output.put_line('msg2var = ' || msg2var);
end;

Output

nameIn = yawin
msg1 = Hello yawin
msg2 = Welcome yawin
msgOut = Hello yawin
msg2Out = Welcome yawin
msg1var = Hello yawin
msg2var = Welcome yawin


PL/SQL procedure successfully completed.


Console Output

If you are using SQL Developer, the output will not be shown in the SQL Developer. The console output is disabled in the SQL Developer by default. The following command should be executed to enable the console output before executing the oracle stored procedure.

SET serveroutput on;

The following command is used to disable console output.

SET serveroutput off;


Leave a Reply