How To Use Table Of Number In Select Into Statment

I'd like to compare the PRIMITIVUMNUMMER (NOT NULL NUMBER(38)) of table OS_CW.CW_FELDDATEN attribute with the values in a local array, v_list_pNummber. This is what I have so far:

Solution 1:

SQL cannot use types declared in local PL/SQL scope. You need to define it in SQL (*):

SQL>create TYPE array_of_numbers ISTABLEOF NUMBER ;
  2/

Type created.

SQL>

Then use the TABLE() operator to convert the first collection into a sub-query you can reference with the IN operator:

SQL>set serveroutput onSQL>declare2      v_list_parentID array_of_numbers;
  3      v_list_pNummer array_of_numbers;
  4begin5select dbuid bulk collectinto v_list_parentID
  6from v_catalog
  7where parentid =1;
  8      dbms_output.put_line('v_list_parentID count = '|| v_list_parentID.count());
  910select primitivumnummer bulk collectinto v_list_pNummer
 11from cw_felddaten
 12where katalog in (select*fromtable( v_list_parentID));
 1314      dbms_output.put_line('v_list_pNummer count = '|| v_list_pNummer.count());
 15end;
 16/
v_list_parentID count =4
v_list_pNummer count =24

PL/SQLprocedure successfully completed.

SQL>

The MEMBER OF syntax works too. It's less typing but may not perform as well as the TABLE() operator if CW_FELDDATEN has a lot of rows.

SQL>declare2      v_list_parentID array_of_numbers;
  3      v_list_pNummer array_of_numbers;
  4begin5select dbuid bulk collectinto v_list_parentID
  6from v_catalog
  7where parent_id =1;
  8      dbms_output.put_line('v_list_parentID count = '|| v_list_parentID.count());
  910select primitivumnummer bulk collectinto v_list_pnummer
 11from cw_felddaten
 12where katalog memberof v_list_parentID;
 1314      dbms_output.put_line('v_list_pNummer count = '|| v_list_pNummer.count());
 15end;
 16/
v_list_parentID count =4
v_list_pNummer count =24

PL/SQLprocedure successfully completed.

SQL>

(*) In 12c we can use types declared in a package spec in SQL.

Solution 2:

You need to use MEMBER OF clause to build a inlist in SQL statement. Also note that its not possible to create a type within the anonymous block and use it in a SQL statement in clause like you had done. You must declare the type outside of the PLSQL block and then use it :

CREATEOR REPLACE TYPE array_of_numbers ISTABLEOF NUMBER ;
/DECLARE

   v_list_parentID   array_of_numbers;
   v_list_pNummer    array_of_numbers;
BEGINSELECT DBUID
     BULK COLLECTINTO v_list_parentID
     FROM OS_SYS.V_CATALOG
    WHERE PARENTID =1;

   SELECT PRIMITIVUMNUMMER
     BULK COLLECTINTO v_list_pNummer
     FROM OS_CW.CW_FELDDATEN
    WHERE KATALOG MEMBEROF v_list_parentID;
END;

See more here: http://www.oracle-developer.net/display.php?id=301