Skip to Main Content
  • Questions
  • Writing a function which accepts unlimited parameters

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Milind.

Asked: September 30, 2008 - 2:11 am UTC

Last updated: October 01, 2008 - 11:55 am UTC

Version: 9.2

Viewed 1000+ times

You Asked

Dear Tom,

How can I create a function in Oracle which can accept unlimited number of parameters like decode function?

Thanks in advance

Milind

and Tom said...

ops$tkyte%ORA9IR2> create or replace type my_args as table of varchar2(4000)
  2  /

Type created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace function foo( p_inputs in my_args ) return varchar2
  2  as
  3          l_str long;
  4  begin
  5          for i in 1 .. p_inputs.count
  6          loop
  7                  l_str := l_str || ', ' || p_inputs(i);
  8          end loop;
  9          return substr( l_str, 3 );
 10  end;
 11  /

Function created.

ops$tkyte%ORA9IR2> show errors
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select foo( my_args('a','b','1','2') ) from dual;

FOO(MY_ARGS('A','B','1','2'))
-------------------------------------------------------------------------------
a, b, 1, 2

ops$tkyte%ORA9IR2> select foo( my_args('x','y',1,sysdate,'a','b','1','2') ) from dual;

FOO(MY_ARGS('X','Y',1,SYSDATE,'A','B','1','2'))
-------------------------------------------------------------------------------
x, y, 1, 30-SEP-08, a, b, 1, 2


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yeah, but how does Decode work?

Mark, September 30, 2008 - 7:27 pm UTC

That's nice and all, but how exactly does Decode work? It doesn't require a table type parameter, it just takes individual parameters, almost like the params keyword in C (except decode seems to have a 255 parameter limit).

Oh sure, Oracle could have just kludged together a pl/sql function with 255 parameters, all defaulted to null, plus a boatload of conditional logic, but I'm guessing that isn't what they did given how well it performs. Something in pro c perhaps? Or is Decode truly a special case of something we can't duplicate?
Tom Kyte
October 01, 2008 - 11:55 am UTC

decode is something you cannot duplicate - it is a builtin function. With builtin's we can do whatever we like.

And C uses varargs
http://weblogs.asp.net/whaggard/archive/2004/07/03/172616.aspx

params belongs to a closed language.

See earlier question ""User-defined functions with arbitrary #s of arguments"

Duke Ganote, September 30, 2008 - 8:57 pm UTC

Milind Chaudhari, October 01, 2008 - 1:02 am UTC

Thanks a lot Tom for (as usual) prompt reply.
Thanks Duke for additional link.

functtion

A reader, October 01, 2008 - 1:01 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library