Home » SQL & PL/SQL » SQL & PL/SQL » can we write function with variable number of argument
can we write function with variable number of argument [message #253963] Wed, 25 July 2007 06:44 Go to next message
kukreja
Messages: 33
Registered: February 2007
Member
Hi
Can anybody tell that can we pass variable number of arguments to a function in oracle 10gR2.
As in function decode we can pass variable no. of arguments upto 255 arguments, similarly can we creat a function which accept any no. of variables.

[Updated on: Wed, 25 July 2007 06:45]

Report message to a moderator

Re: can we write function with variable number of argument [message #253976 is a reply to message #253963] Wed, 25 July 2007 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to declare all the variables with a default value.
DECODE is an internal function and not PL/SQL one, you can't have the same thing.

Regards
Michel
Re: can we write function with variable number of argument [message #253984 is a reply to message #253976] Wed, 25 July 2007 07:35 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I replied at the OTN forums, but let me repeat myself: I would use a table type:
SQL> set serverout on
SQL> CREATE TYPE mytabtype IS TABLE OF VARCHAR2(30);
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE myproc(myparam IN mytabtype)
  2  IS
  3  BEGIN
  4     dbms_output.put_line('Number of arguments: '||myparam.COUNT);
  5
  6     FOR i in 1..myparam.COUNT
  7     LOOP
  8             dbms_output.put_line('Argument :'||myparam(i));
  9     END LOOP;
 10  END;
 11  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL> begin
  2      myproc(mytabtype('A'));
  3      myproc(mytabtype('A','B'));
  4  end;
  5  /
Number of arguments: 1
Argument :A
Number of arguments: 2
Argument :A
Argument :B

PL/SQL procedure successfully completed.

SQL>
SQL> DROP PROCEDURE myproc
  2  /

Procedure dropped.

SQL> DROP TYPE mytabtype
  2  /

Type dropped.


DECODE is a part of the STANDARD package. It is a special case, using a special pragma (PRAGMA BUILTIN).

MHE
Previous Topic: Will the code be executed ???
Next Topic: temporary table
Goto Forum:
  


Current Time: Fri Dec 09 11:53:01 CST 2016

Total time taken to generate the page: 0.10373 seconds