CODEWITHSUNDEEP
oracle-databaseplsqloptional-parameters
Ram
Ram

Reputation: 21

ORACLE: Creating a oracle procedure with optional parameters

It is common to have pages with filters in most asp.net application (usually have 5 or more criterias, EX: filtr by:lastname, firstname, date, etc..).

Mostly dealing with TSQL (SQL Server 2000/2005) . I would deal with criteria(s) in my procedures, for example:

PARAMETERS Param1 VARCHAR(32), Param2 INT; 

SELECT columns FROM table WHERE 
    ([Param1] IS NULL OR column1 = [Param1]) 
    AND 
    ([Param2] IS NULL OR column2 = [Param2])

Can someone show me how optional parameters are dealt PL/SQL, Thanks

Upvotes: 1

Views: 7525

Answers (1)

Aleksej
Aleksej

Reputation: 22949

For example:

create or replace procedure optPar(a in number, b in number := null, c number := 999) is
begin
    dbms_output.put_line(a || ', ' || b || ', ' || c);
end;

The procedure needs the a parameter:

SQL> exec optPar();
BEGIN optPar(); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OPTPAR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

You can omit b, c:

SQL> exec optPar(1);
1, , 999

PL/SQL procedure successfully completed.

SQL> exec optPar(1, 2);
1, 2, 999

PL/SQL procedure successfully completed.

SQL> exec optPar(1, 2, 3);
1, 2, 3

PL/SQL procedure successfully completed.

In this case, explicitly passing parameters is even more important then with no optional parameters, to easily understand which value you are giving to each parameter:

SQL> exec optPar( a=> 1, c=> 3);
1, , 3

PL/SQL procedure successfully completed.

Upvotes: 5

Related Questions