I found that you can use a Table function to incorporate a PL/SQL collection in a query. I needed to take a multiple value parameter and some how get it into a PL/SQL collection. This multiple value parameter will be a list of values delimited by a coma. For the PL/SQL collection, I decided to use a nested table.
In summary this is what I need to do:
- Read a multiple value parameter.
- Undelimit the values.
- Put each value in its own row of a nested table.
- Use the Table function to query against the nested table.
For this example I will use the Scott schema. This example will take a parameter with multiple EMPNO numbers and return the sum of the salaries. To test then it would be like this:
select TEST_PACKAGE.GET_SALARY_TOTAL('7654,7566,7876') total_salary from dual
I am going use a little bit of the object oriented programming of PL/SQL.
Run the script below to create a Type object varchar2. Then the second part of the script will create a nested table Type for the multiple values:
create or replace
TYPE t_tf_row AS OBJECT (
empno VARCHAR2(5));
create or replace
TYPE t_tf_tab IS TABLE OF t_tf_row;
Here is the PL/SQL package:
create or replace
PACKAGE TEST_PACKAGE AS
FUNCTION GET_SALARY_TOTAL(P_EMPLOYEE_LIST IN VARCHAR2) RETURN NUMBER;
FUNCTION GET_ROWS(p_employee_list varchar2) RETURN t_tf_tab;
END TEST_PACKAGE;
/
create or replace
PACKAGE BODY TEST_PACKAGE AS
FUNCTION GET_SALARY_TOTAL(P_EMPLOYEE_LIST IN VARCHAR2) RETURN NUMBER AS
SUM_SAL NUMBER;
BEGIN
select sum(e.sal)
into sum_sal
from emp e
where e.empno in (select empno from table( get_rows(p_employee_list) ));
RETURN SUM_SAL;
END GET_SALARY_TOTAL;
FUNCTION GET_ROWS(p_employee_list in varchar2) RETURN t_tf_tab AS
EMPS t_tf_tab := t_tf_tab();
t_len BINARY_INTEGER;
l_start integer;
l_end integer;
BEGIN
IF p_employee_list is null then
t_len := 0;
else
t_len := REGEXP_COUNT(p_employee_list,',',1) + 1;
end if;
l_start := 1;
for i in 1..t_len
loop
EMPS.EXTEND;
if instr(p_employee_list,',',1,i) = 0 then
EMPS(EMPS.LAST) := t_tf_row(substr(p_employee_list,l_start));
else
l_end := instr(p_employee_list,',',1,i);
EMPS(EMPS.LAST) := t_tf_row(substr(p_employee_list,l_start,l_end - l_start));
l_start := instr(p_employee_list,',',1,i) + 1;
end if;
end loop;
return(EMPS);
END GET_ROWS;
END TEST_PACKAGE;
/
The function GET_ROWS takes in the multiple value parameter and returns a nested table that is populated. This is not to say it is the best way to accomplish this but it is the solution I came up with.
