SQL expression is of wrong type

Question

Trying to run the following on SQL developer

declare
    type hType is table of number not null
        index by BINARY_INTEGER;
    items_v hType;
    references_v hType;
    keyId number;

begin   
    keyId := 1234567;

    select i.item bulk collect into items_v
    from items i
    where i.item_id = ...;

    select j.reference bulk collect into references_v
    from references j
    where j.reference = ..
        and j.item in items_v;
end;

I get ORA-06550: line 23, column 31: PLS-00382: expression is of the wrong type

Even though j.item and i.item are also of type NUMBER;

score 1 · Accepted Answer · answered Dec 27 '17 at 05:43

Until 11g, you cannot declare a TYPE in PLSQL scope and use it in SQL statement within a PLSQL block. You need to declare the TYPE under the scope of SQL , if you want to use it under PLSQL block. Also when you wanted to use one collection in IN clause, then MEMBER OF can be used. See below:

Type Created in SQL Scope.

SQL> CREATE OR REPLACE TYPE HTYPE IS TABLE OF NUMBER NOT NULL ;
        /

Type created.

Anonymous Block

SQL> DECLARE   
  2       ITEMS_V                       HTYPE;
  3       REFERENCES_V                  HTYPE;
  4       KEYID                         NUMBER;
  5  BEGIN
  6       KEYID := 1234567;
  7  
  8       SELECT I.ITEM
  9       BULK COLLECT INTO ITEMS_V
 10         FROM ITEMS I
 11        WHERE I.ITEM_ID = 1;
 12  
 13       SELECT J.REFERENCE
 14       BULK COLLECT INTO REFERENCES_V
 15         FROM REFERENCES J
 16        WHERE J.REFERENCE = 1
 17        AND J.ITEM MEMBER OF ITEMS_V;--<--Used Member of to check elements of colection
 18  END;
 19  /

PL/SQL procedure successfully completed.

You can use use IN clause as:

SQL> DECLARE   
  2       ITEMS_V                       HTYPE;
  3       REFERENCES_V                  HTYPE;
  4       KEYID                         NUMBER;
  5  BEGIN
  6       KEYID := 1234567;
  7  
  8       SELECT I.ITEM
  9       BULK COLLECT INTO ITEMS_V
 10         FROM ITEMS I
 11        WHERE I.ITEM_ID = 1;
 12  
 13       SELECT J.REFERENCE
 14       BULK COLLECT INTO REFERENCES_V
 15         FROM REFERENCES J
 16        WHERE J.REFERENCE = 1
 17        AND J.ITEM IN (SELECT COLUMN_VALUE FROM TABLE(ITEMS_V)); --<-- Used IN Clause.
 18  END;
 19  /

PL/SQL procedure successfully completed.

Thank you @XING, this was really helpful! – James Blues Jan 02 '18 at 06:50 — James Blues, Jan 02 '18 at 06:50

score 0 · Answer 2 · answered Dec 26 '17 at 10:51

In the REFERENCES part of your query, you can't use ITEMS_V collection like that; consider using something like this instead:

for i in items_v.first .. items_v.last loop
  select j.reference bulk collect into references_v
    from references j
    where j.reference = ..
        and j.item_id = items_v(i);
end loop;

There might be a smarter way to do that (hopefully, someone else will show you how), but this should work as well.

SQL expression is of wrong type

2 Answers2