Using the default value of a function parameter

craig · **Joined:** Tue Feb 10, 2009 5:18 pm **Posts:** 77

I want to call a function that has a number of parameters that have default values. I only want to specify the first and last parameter value and accept the default for all the others. I am calling the function in SQL, so i cannot name the parameters. Has anybody got an idea on how i can specify that i want the default value?

an example of my function is

Code:

funtion test_func(p_1 in number, p_2 in number default 1, p_3 in number default 1, p_4 in number default 2, p_5 in number) return varchar2

my query would look something like

Code:

select test_func(1, default, default,default, 3)
from dual

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

Hi Craig,
I am afraid you cannot call a function by specifying the first and last paramter to accept default values.
You can only give first set of default parameters and miss the later ones that will take the default parameters itself.
But there is a workaround if create a wrapper function that calls your desired function using named associations.

Consider a function below

Code:

CREATE OR REPLACE FUNCTION c_test (P_deptno    NUMBER DEFAULT 10 ,
                                   p_JOB       VARCHAR2 DEFAULT 'CLERK' )
   RETURN NUMBER
IS
   max_sal   NUMBER;
BEGIN
   SELECT   MAX (sal)
     INTO   max_sal
     FROM   emp
    WHERE   deptno = p_deptno AND p_job = p_job;

   RETURN max_sal;
END;
/

I will run following queries

Code:

select c_test(10) from dual;
/

C_TEST(10)
----------
5000
1 row selected.

Code:

select c_test() from dual;
/

C_TEST()
----------
5000
1 row selected.

Code:

select c_test(default) from dual;
/

this results in error

WORKAround

Code:

create or replace function d_test(pjob varchar2 default 'CLERK')
return number is
begin
return c_test(p_job=>pjob);

end;

select d_test('CLERK') from dual;

Hope it solves your problem

Using the default value of a function parameter

Who is online