How to access just one OUT value from a Procedure that returns multiple OUT values?

Question

I have a PL/SQL procedure that has a single IN/OUT value and multiple OUT values.

The Procedure works fine and I am able to read all the OUT values.

What I now wish to do is to call the Procedure from within another Procedure but only access one of the returned OUT values.

for example:

  PROCEDURE MyProc
    (
    --INPUT/OUTPUT PARAMETERS
    io_myVar IN OUT NUMBER,
    o_TestNum1 OUT NUMBER,
    o_TestNum2 OUT NUMBER,
    o_Test1 OUT VARCHAR2
    );

Now I want to call this procedure as follows:

MyProc(v_Input, , , v_TestOutput);

This is obviously simplified but some of the values returned are in fact custom types and custom table/record types so scold be quite large.

I don't want to have to create variables just to store all this data that I don't need to process at this time - I am only interested in one of the returned values.

How do I call the Procedure and only get the one value I am interested in?

Answer 1

Every OUT parameter must be set, but you can use the same dummy variable multiple times for the outputs you don't care about.

For example, the below code assigns the OUT variable we care about to V_IMPORTANT. The other two variables are both assigned to V_DUMMY. It's not a perfect solution but it cuts down on the extra code at least a little.

declare
    v_important number;
    v_dummy     number;

    --Procedure with lots of OUT variables.
    procedure myProc(a out number, b out number, c out number) is
    begin
        a := 1; b := 2; c := 3;
    end;
begin
    myProc(v_important, v_dummy, v_dummy);
end;
/