ORACLE: Creating a oracle procedure with optional parameters

Question

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

Pretty much the same way.

Gordon Linoff
– Gordon Linoff

2017-04-16 03:22:52 +00:00
Commented Apr 16, 2017 at 3:22 — Gordon Linoff
– Gordon Linoff, Commented Apr 16, 2017 at 3:22

Aleksej · Accepted Answer · 2017-04-16 07:00:00Z

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.

Collectives™ on Stack Overflow

ORACLE: Creating a oracle procedure with optional parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related