CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Oriol Gallart
Oriol Gallart

Reputation: 41

Can I know the number of parameters passed in stored procedure in Oracle PL/SQL

I have a stored procedure in a Oracle Database that receives 3 parameters.

I know that I call it with 1 to 3 parameters but it's possible to know inside itself how many arguments are the defaults or are really passed?

For example:

I ask this because I worked with Informix 4GL and I could use "NARGS" to know the number of arguments that I receive.

Upvotes: 0

Views: 2354

Answers (2)

M. Kemp
M. Kemp

Reputation: 131

Similar to ShoeLace's answer, I think counting the number of parameters that do not equal the default of each parameter would work.

The key to this is to give each parameter a nonsensical default value. Like '~#dummee_v@1u3#~' maybe. Anything you are confident will never actually be passed in. This way you don't have to worry about somebody passing in a parameter value that equals the default value.

So:

create procedure p1 (id1 varchar2 default '~#dummee_v@1u3#~', id2 varchar2 default '~#dummee_v@1u3#~') is
     lParamCount number := 0;
     lDummyParamValue varchar2 := '~#dummee_v@1u3#~';
     begin
          if id1 <> lDummyParamValue then lParamCount := lParamCount + 1;
          if id2 <> lDummyParamValue then lParamCount := lParamCount + 1;
     end p1;

Upvotes: 0

ShoeLace
ShoeLace

Reputation: 3576

The short answer is no.. there is not an equivalent to NARGS or "C"s argc.

if you are using null default values you could manually count the number of arguments that do no equal the default value.. but that wont tell you if you explicitly pass the default value as a parameter.

i can think of 2 solutions. 1. user overloaded procedures .. ie 

procedure a (p_1 number);
procedure a (p_1 number, p_2 number);
procedure a (p_1 number, p_2 number, p_3 number);

then in the bodies you would "know" by which one you are in.

option 2. pass a varray/plsql table as a single argument but then actually passing the arguments becomes problematic.

create or replace package x
is

type an_arg is record ( n number, v varchar2(2000), d date);
type args is table of an_arg;

procedure a(argv args);

end;
/

create or replace package body x
is

procedure a(argv args)
is
begin
    dbms_output.put_line('i was passed '||argv.count||' arguments');

end;

end;
/

Upvotes: 3

Related Questions