CODEWITHSUNDEEP
sqloracle-databaseplsql
Abdul Mubeen
Abdul Mubeen

Reputation: 31

Using Table type in IN-clause in PLSQL procedure

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

Answers (1)

Nick Krasnov
Nick Krasnov

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

Related Questions