Reputation: 31
I have a procedure which takes table type input parameter. Now I have to use this parameter in IN-clause of SELECT query.
CREATE TYPE ids IS TABLE OF NUMBER;
CREATE PROCEDURE (emp_ids IN ids) IS
CURSOR IS (SELECT * FROM EMPLOYEES WHERE EMP_ID IN (SELECT * FROM TABLE(emp_ids)); .....
But I found that this code is not going to work because local collection types cannot be used in an SQL statement.
Is there any alternate way to achieve using table type parameter in a SELECT statement?
Upvotes: 2
Views: 5092
Reputation: 27251
According to what you have posted, you are declaring collection as schema object, not the local type. This means that you shouldn't have any problems of using it. Here is an example:
-- collection type as schema object
SQL> create or replace type ids is table of number;
2 /
Type created
SQL> create or replace procedure proc1 (emp_ids IN ids)
2 IS
3 cursor c is (
4 select first_name
5 from employees
6 where employee_id in (select column_value
7 from table(emp_ids)
8 )
9 );
10 begin
11 for i in c
12 loop
13 dbms_output.put_line(i.first_name);
14 end loop;
15 end;
16 /
Procedure created
SQL> exec proc1(ids(101, 103, 200));
Neena
Alexander
Jennifer
PL/SQL procedure successfully completed
Upvotes: 2