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?

score 1 · Answer 1 · answered Mar 06 '19 at 18:36

To achive this you must define for each parameter combination a wrapper procedure.

For example to get only the last parameter you must define:

create PROCEDURE MyProc2
    (
    io_myVar IN OUT NUMBER,
    o_Test1 OUT VARCHAR2
    ) as
v2 NUMBER; /* will be ignored */
v3 NUMBER;    
begin
  MyProc(io_myVar,v2,v3,o_Test1);
end;
/

score 1 · Accepted Answer · answered Mar 07 '19 at 03:47

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;
/

answered Mar 07 '19 at 03:47

Jon Heller

34,999
6
74
132

This is looking like my best option, thanks. One complication (possibly not clear from my trivial example) is that all the OUT values are different types, some of them custom tables/records. How do I reuse the same v_dummy in this case? – Stephen Mar 07 '19 at 10:40
@Stephen Unfortunately you must create a separate dummy variable for each type. – Jon Heller Mar 08 '19 at 05:35

score 0 · Answer 3 · answered Mar 06 '19 at 17:40

You cannot. See full Tom Kyte's answer here. The quote:

Parameter Mode  Is Default?          Role

IN              Default mode         Passes a value to the subprogram.

OUT             Must be specified.   Returns a value to the invoker.

IN OUT          Must be specified.   Passes an initial value to the subprogram and returns an updated value to the invoker.

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

3 Answers3