CODEWITHSUNDEEP
sqloracle-databaseplsqluser-defined-types
user2852537
user2852537

Reputation: 15

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:

DECLARE
    TYPE array_of_numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    v_list_parentID     array_of_numbers;
    v_list_pNummer      array_of_numbers;
BEGIN
    SELECT DBUID BULK COLLECT INTO v_list_parentID 
    FROM OS_SYS.V_CATALOG 
    WHERE PARENTID = 1;

    SELECT PRIMITIVUMNUMMER BULK COLLECT INTO v_list_pNummer 
    FROM OS_CW.CW_FELDDATEN 
    WHERE KATALOG IN (v_list_parentID);
END;

When running the code above I get this error:

Error report -
ORA-06550: line 28, column 104:
PLS-00382: expression is of wrong type
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

How can I compare a column (number) with a table NUMBER INDEX BY BINARY_INTEGER?

Thanks!

Upvotes: 1

Views: 6823

Answers (2)

APC
APC

Reputation: 146239

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

SQL> create TYPE array_of_numbers IS TABLE OF 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 on
SQL> declare
  2      v_list_parentID array_of_numbers;
  3      v_list_pNummer array_of_numbers;
  4  begin
  5      select dbuid bulk collect into v_list_parentID
  6          from v_catalog
  7          where parentid = 1;
  8      dbms_output.put_line('v_list_parentID count = ' || v_list_parentID.count());
  9
 10      select primitivumnummer bulk collect into v_list_pNummer
 11          from cw_felddaten
 12          where katalog in (select * from table( v_list_parentID));
 13
 14      dbms_output.put_line('v_list_pNummer count = ' || v_list_pNummer.count());
 15  end;
 16  /
v_list_parentID count = 4
v_list_pNummer count = 24

PL/SQL procedure 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> declare
  2      v_list_parentID array_of_numbers;
  3      v_list_pNummer array_of_numbers;
  4  begin
  5      select dbuid bulk collect into v_list_parentID
  6          from v_catalog
  7          where parent_id = 1;
  8      dbms_output.put_line('v_list_parentID count = ' || v_list_parentID.count());
  9
 10      select primitivumnummer bulk collect into v_list_pnummer
 11          from cw_felddaten
 12          where katalog member of v_list_parentID;
 13
 14      dbms_output.put_line('v_list_pNummer count = ' || v_list_pNummer.count());
 15  end;
 16  /
v_list_parentID count = 4
v_list_pNummer count = 24

PL/SQL procedure successfully completed.

SQL>

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

Upvotes: 5

XING
XING

Reputation: 9886

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 :

CREATE OR REPLACE TYPE array_of_numbers IS TABLE OF NUMBER ;
/
DECLARE

   v_list_parentID   array_of_numbers;
   v_list_pNummer    array_of_numbers;
BEGIN
   SELECT DBUID
     BULK COLLECT INTO v_list_parentID
     FROM OS_SYS.V_CATALOG
    WHERE PARENTID = 1;

   SELECT PRIMITIVUMNUMMER
     BULK COLLECT INTO v_list_pNummer
     FROM OS_CW.CW_FELDDATEN
    WHERE KATALOG MEMBER OF v_list_parentID;
END;

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

Upvotes: 2

Related Questions