Set Default Parameter as a list of strings Oracle PLSQL

Question

Here is what I am working with:

CREATE OR REPLACE PROCEDURE SPROCLABPROCEDURE (Dept in VARCHAR2 DEFAULT
'Administration', 'Marketing', 'Purchasing', 'Human Resources', 'Shipping', 'IT',
'Public Relations', 'Sales', 'Executive', 'Finance', 'Accounting',
Mgr in VARCHAR2 DEFAULT NULL) AS

vManager Varchar2(30) := Mgr;
vDepartment Varchar2(30) := Dept;

I want the default values to be that entire list of strings. Later in my code, I want the option to be able to specify a few managers, or a few departments, or not input anything, so that the entire list is returned:

WHERE m.first_name ||' ' || m.last_name IN (vManager) -- NULL if I don't input anything
  OR d.department_name IN (vDepartment) -- All departments if I don't input anything

When I call the function, and don't input anything, I'm expecting to return all possible rows. That is the desired effect.

score 1 · Answer 1 · answered Mar 28 '14 at 06:59

In order to be able to pass a list of strings as a parameter you should change the parameter to a list.

CREATE OR REPLACE TYPE t_departments IS TABLE OF VARCHAR2(30);

And then create your procedure based on that new type:

CREATE OR REPLACE PROCEDURE SPROCLABPROCEDURE (Dept IN t_departments DEFAULT t_departments('Administration', 'Marketing', 'Purchasing', 'Human Resources', 'Shipping', 'IT','Public Relations', 'Sales', 'Executive', 'Finance', 'Accounting'), Mgr IN VARCHAR2 DEFAULT NULL) AS

Later you could use MEMBER OF instead of IN to check if a certain value is in the table:

 ...
 WHERE d.department_name MEMBER OF Dept 
 ...

Alternatively, if you can't change the type of the procedure parameters, you could just use variables:

CREATE OR REPLACE PROCEDURE SPROCLABPROCEDURE (Dept IN VARCHAR2 DEFAULT NULL, Mgr IN VARCHAR2 DEFAULT NULL) AS
  TYPE t_departments IS TABLE OF VARCHAR2(30);
  v_all_departments t_departments := t_departments('Administration', 'Marketing', 'Purchasing', 'Human Resources', 'Shipping', 'IT','Public Relations', 'Sales', 'Executive', 'Finance', 'Accounting');
  vManager VARCHAR2(30);
  vDepartment t_departments;
BEGIN
  IF Dept IS NULL THEN
    vDepartment := v_all_departments;
  ELSE
    vDepartment := t_departments(Dept);
  END IF;

  ...

  WHERE d.department_name MEMBER OF vDepartment

  ...

END;

score 0 · Answer 2 · edited May 23 '17 at 12:11

I think something is unclear here...

First, this is a procedure - not a function, therefor it will not return a thing.

A varchar is not an array.
When you are getting or returning a varchar2 - there is only one possible string to pass back and forth... If you want to use a list - you should check the array types PL/SQL offers. http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

Third - if you have constant values, in a database the place for it is within a table, not as constants in a procedure.

I would recommend you to check out Temporary tables - they're a great data facilitator for "within session" communication for a list that you would like to return. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5824706700346810645
These would be a great "in session"/"in connection" joins, like the one you described above (select ... where last_name in (select manager from temptable) for example).

Another thing - using the last name for identifying records in the DB is not the way you wanna go. You can use unique identifiers for that, and foreign keys. (if you are unfamiliar with the terms - What are database constraints? .

If I did not answer your question (or pointed you out to the answer) - please clarify your question, since I am not sure (probably as many others) what exactly you want to do (logically).

score 0 · Answer 3 · answered Mar 28 '14 at 06:00

It's a quite unclear what you're asking but here is a minimal example how you can pass a list of values that can be used as a part of a SQL query to a PL/SQL subprogram:

-- SQL type so that can be used in SQL context (the select-statement)
create or replace type strlist_t is table of varchar2(4000);
/

create or replace procedure foo(p_owners in strlist_t default strlist_t('JANI')) is
begin
  for i in (select * from all_tables 
             where owner in (select * from table(p_owners)))
  loop
    dbms_output.put_line(i.owner || '.' || i.table_name);
  end loop;
end;
/
show errors

begin
  -- define your own criteria
  foo(strlist_t('SYS', 'JANI'));
  -- use the default criteria
  foo;
end;
/

Hope this helps !

Instead of `where owner in (select * from table(p_owners))` use `where owner member of p_owners`. — user272735, Mar 28 '14 at 07:41

Set Default Parameter as a list of strings Oracle PLSQL

3 Answers3