Re: Can we pass variable paramaters to a procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 20 Apr 1999 13:21:31 GMT
Message-ID: <371d7671.2482249@192.86.155.100>

A copy of this was sent to "deb" <dnanda_at_netcom.ca> (if that email address didn't require changing) On Mon, 19 Apr 1999 18:46:38 -0400, you wrote:

>For those of you who are familiar with C programming, you can check the
>number of arguments to a function with the help of argc/argv. I was
>wondering if there is some way to do a similar thing for an Oracle
>procedure?
>
>Can I set the number of parameters that a procedure accepts as variable and
>then within the procedure count the number of actual values entered by the
>user?
>

Yes, if you want to pass arguments in the same way they are to the C main() function. A C main() looks like:

void main( int argc, char * argv[], char * env[] )

It takes:

argc = count of the args pointed to by argv
argv = array of pointers to strings
env  = array of pointers to environment strings (you know when you've gotten to

the end of this array when env[i] == NULL )

To do this in plsql, you would:

SQL> create or replace package types
  2 as
  3 type myArray is table of varchar2(2000) index by binary_integer;   4 end;
  5 /

Package created.

SQL> 
SQL> 
SQL> create or replace procedure main( argv in types.myArray )

  2 is
  3 begin

  4          for i in 1 .. argv.count loop
  5          dbms_output.put_line( 'argv(' || i || ') = ' || argv(i) );
  6      end loop;

  7 end;
  8 /

Procedure created.

SQL>
SQL> declare
  2 args types.myArray;
  3 begin

  4          args(1) := 'Some Data';
  5          args(2) := 'Some More Data';
  6          args(3) := 'The Last of the Data';
  7  
  7          main( args );

  8 end;
  9 /

argv(1) = Some Data
argv(2) = Some More Data
argv(3) = The Last of the Data

PL/SQL procedure successfully completed.

Another way to do it might be to use DEFAULT parameters, for example:

create procedure main( arg1 in varchar2 DEFAULT NULL,

                       arg2 in varchar2 DEFAULT NULL,
                       ...
                       argN in varchar2 DEFAULT NULL )

as
....

Now i can call:

exec main

exec main( 'x' );
exec main( 'x', 'y' );
exec main( 'x', 'y', 'z' );
exec main( 'x', argN => '5' );

and so on...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Apr 20 1999 - 08:21:31 CDT