Array in IN() clause oracle PLSQL

by

Assuming that your collection is defined in SQL, not just in PL/SQL, you can use the TABLE operator (the definition you posted isn’t syntactically valid– you’d need to specify a length for the VARCHAR2)

AND p.plc_status IN (SELECT column_value
                       FROM TABLE( plcListchar ))

Since I don’t have your tables, an example using the SCOTT schema

SQL> create type ename_tbl is table of varchar2(30);
  2  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_enames ename_tbl := ename_tbl( 'KING', 'SMITH' );
  3  begin
  4    for i in (select *
  5                from emp
  6               where ename in (select column_value
  7                                 from table( l_enames )))
  8    loop
  9      dbms_output.put_line( 'ENAME = ' || i.ename );
 10    end loop;
 11* end;
SQL> /
ENAME = KING
ENAME = SMITH

PL/SQL procedure successfully completed.

More Related Contents:

  1. Solution to “cannot perform a DML operation inside a query”?
  2. How to use Alias in Where clause? [duplicate]
  3. Receive an unexpected symbol error in my PLSQL query [closed]
  4. Splitting string into multiple rows in Oracle
  5. Search All Fields In All Tables For A Specific Value (Oracle)
  6. Paging with Oracle
  7. What is the string concatenation operator in Oracle?
  8. What is the difference between function and procedure in PL/SQL?
  9. SQL recursive query on self referencing table (Oracle)
  10. How can you tell if a value is not numeric in Oracle?
  11. PLS-00428: an INTO clause is expected in this SELECT statement
  12. passing table and column name dynamically using bind variables
  13. SQL error “ORA-01722: invalid number”
  14. Is there any way to flush output from PL/SQL in Oracle?
  15. PLSQL Insert into with subquery and returning clause
  16. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  17. What does the colon sign “:” do in a SQL query?
  18. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  19. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  20. Advice Using Pivot Table in Oracle
  21. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  22. Inserting into Oracle and retrieving the generated sequence ID
  23. How to handle optional parameters in SQL query?
  24. How do I call an Oracle stored procedure from an Excel VBA script?
  25. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  26. Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
  27. How to check if a column exists before adding it to an existing table in PL/SQL?
  28. Subtracting Dates in Oracle – Number or Interval Datatype?
  29. How to call Oracle MD5 hash function?
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment