CODEWITHSUNDEEP
oracle-databaseplsql
p.han
p.han

Reputation: 3

Calling oracle procedure without putting all parameters

I'm using oracle procedure which looks like this

CREATE PROCEDURE spName (
    pCurosr OUT refcursor,
    par2 IN VARCHAR := NULL,
        ....
        ....
    par98 IN VARCHAR := NULL,
    error OUT NUMBER
) AS
BEGIN
    ....
    ....
END

Problem is, some procedures take too many parameters which have been set NULL as default value. What I want to know is, whether it is possible to exec procedure like this?

exec spName(:refcur, :par5 = 'value', :error);

Only supply parameters that I want and all other parameters are set as default values.

Upvotes: 0

Views: 1126

Answers (1)

Aleksej
Aleksej

Reputation: 22969

For example, with a procedure like this

create or replace procedure testProc ( pOut OUT number,
                                       pIn1 IN  number := null,
                                       pIn2 IN  number := null,
                                       pIn3 IN  number := null,
                                       pIn4 IN  number := null
                                     ) is ...

You can use named parameter assignment: 

declare
    vOut number;
begin
    testProc (pOut => vOut,
              pIn3 => 3
             );             
end;

The unnamed parameters will take the default value.

The order of named parameters is not important here, so you can do, for example, 

testProc (pIn1 => 1,
          pOut => vOut,
          pIn3 => 3
         );

Upvotes: 6

Related Questions