Array in IN() clause oracle PLSQL

ghz 1years ago ⋅ 425 views

Question

I am passing String array(plcListchar) to Stored Procedure, i would like to use this String array in IN() clause.

i can not use plcListchar directly in IN() clause. Let me show how i am crating plcListchar string array in JAVA.

 String array[] = {"o", "l"}; 
 ArrayDescriptor des = ArrayDescriptor.createDescriptor("CHAR_ARRAY", con);
 ARRAY array_to_pass = new ARRAY(des,con,array);            
 callStmtProductSearch.setArray(4, array_to_pass);

for crating CHAR_ARRAY,

create or replace type CHAR_ARRAY as table of varchar2;

i want use plcListchar in IN clause. the following is my Stored Procedure.

CREATE OR REPLACE PROCEDURE product_search(
            status IN varchar2, 
            plcList IN varchar2,
            i_culture_id IN number,
            plcListchar IN CHAR_ARRAY,
            status_name OUT  varchar2, 
            culture_code  OUT varchar2)
AS  
    CURSOR search_cursor IS
    SELECT p.status_name, p.culture_code
                FROM PRISM_ITEM_cultures@prism p
                WHERE p.publishable_flag=1 
                AND p.isroll =0 
                AND status = '-1'
                AND p.plc_status IN (   );
 BEGIN
           OPEN search_cursor;
           FETCH search_cursor INTO status_name, culture_code ;
           CLOSE search_cursor;
 END;

Could you please suggest me how to use, if you like to suggest any other logic, it is great.

Answer

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.

Note: The code create type ename_tbl is table of varchar2(30); creates a new user-defined type (UDT) in Oracle called ename_tbl. This UDT is a table of varchar2(30) columns, which means that it can store a collection of strings, each of which can be up to 30 bytes long.

#oracle