Use Array Variable (Out from Execute Immediate) in Select statement in store procedurce

Question

I need to use Array Variable (out from Execute Immediate) as Parameter in Select Statement in Store Procedure.

Create new type as Varray (As Below code)

CREATE OR REPLACE TYPE Array_LIST AS VARRAY(200) OF VARCHAR2(10);

Then Create my procedure that return table

CREATE OR REPLACE PROCEDURE SchemaName.ProcedureName
(Query_String IN VARCHAR2, Ref_Cursor OUT SYS_REFCURSOR)

AS
BEGIN
DECLARE COMCODE Array_LIST;

    BEGIN
        EXECUTE IMMEDIATE Query_String BULK COLLECT INTO COMCODE;

            BEGIN
                    Open Ref_Cursor For

                        SELECT
                             Column1, Column2
                        From Table_Name
                        Where
                            Column1 IN (COMCODE);
            END;
    END;
END;

When I executed this procedure, I got below error

==> ORA-00932: inconsistent datatypes : expected NUMBER got Scheman_Name.Array_LIST

Can anyone help me!

alexgibbs alexgibbs · Accepted Answer · 2019-02-08T17:58:34

There is just a small problem in the use of a collection type in a SQL IN clause. Using a collection type directly in an IN clause is not supported. Collection types need to be TABLEd for use in SQL.

Here's a slight modification that compiles and executes ok:

CREATE TABLE TABLE_NAME(COLUMN1 VARCHAR2(32), COLUMN2 VARCHAR2(32));

CREATE OR REPLACE TYPE Array_LIST AS VARRAY(200) OF VARCHAR2(10);

CREATE OR REPLACE PROCEDURE ProcedureName
(Query_String IN VARCHAR2, Ref_Cursor OUT SYS_REFCURSOR)

AS
BEGIN
  DECLARE COMCODE Array_LIST;

  BEGIN
    EXECUTE IMMEDIATE Query_String BULK COLLECT INTO COMCODE;

    BEGIN
      Open Ref_Cursor For

        SELECT
          Column1, Column2
        From Table_Name
        Where
            Column1 IN (SELECT COLUMN_VALUE FROM TABLE(COMCODE));
    END;
  END;
END;
/

Procedure created.

Then it will execute ok:

DECLARE
V_CURSOR SYS_REFCURSOR;
BEGIN
  PROCEDURENAME(Q'!SELECT 'LOREM IPSUM' FROM DUAL!',V_CURSOR);
END;
/


PL/SQL procedure successfully completed.

Use Array Variable (Out from Execute Immediate) in Select statement in store procedurce

1 Answers