PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER

Question

I'm executing PL/SQL code to display the Currency Code from the Failed Reservation table. Object type and Nested Table collections are used.
When the PL/SQL code is run, the following error is generated. The corresponding line is highlighted in the PL/SQL code section.

Error report:

ORA-06550: line 27, column 11:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
ORA-06550: line 27, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The code is pasted below:

DDL - Table creation:

CREATE TABLE FAILEDRESERVATION
(   
    FAILEDRESERVATIONID NUMBER(18,0), 
    FK_TRANSACTIONID NUMBER(18,0), 
    DEBITRESERVATIONID NUMBER(18,0), 
    RESERVATIONTIME DATE, 
    RESERVATIONAMOUNT NUMBER(18,5), 
    CURRENCYCODE CHAR(3 BYTE), 
    AVAILABLEAMOUNT NUMBER(18,5)
);

ALTER TABLE FAILEDRESERVATION 
ADD CONSTRAINT "PK_FAILEDRESERVATION" PRIMARY KEY ("FAILEDRESERVATIONID");

Object Type:

CREATE OR REPLACE TYPE TYPE type_failedreservation AS OBJECT 
(                                     
    FK_TRANSACTIONID     NUMBER(18),    
    DEBITRESERVATIONID   NUMBER(18),    
    RESERVATIONTIME      DATE,          
    RESERVATIONAMOUNT    NUMBER(18,5),  
    CURRENCYCODE         CHAR(3),       
    AVAILABLEAMOUNT      NUMBER(18,5)   
);     

DML:

INSERT INTO FAILEDRESERVATION (FAILEDRESERVATIONID,FK_TRANSACTIONID,DEBITRESERVATIONID,RESERVATIONTIME,RESERVATIONAMOUNT,CURRENCYCODE,AVAILABLEAMOUNT) 
VALUES (289,2,1,to_date('07-MAR-16','DD-MON-RR'),20000,'USD',10000);

INSERT INTO FAILEDRESERVATION (FAILEDRESERVATIONID,FK_TRANSACTIONID,DEBITRESERVATIONID,RESERVATIONTIME,RESERVATIONAMOUNT,CURRENCYCODE,AVAILABLEAMOUNT) 
VALUES (288,1,1,to_date('01-MAR-16','DD-MON-RR'),10000,'NOK',10000);

Nested Tables:

CREATE OR REPLACE TYPE type_failedreservation_coll as TABLE OF type_failedreservation; 

CREATE OR REPLACE TYPE type_dbtrsid_coll AS TABLE OF NUMBER;

PL/SQL Code:

DECLARE    
    P_FAILEDRESERVATION APPDATA.TYPE_FAILEDRESERVATION_COLL;

    vdbtid_coll type_dbtrsid_coll := type_dbtrsid_coll();

BEGIN    
    SELECT TYPE_FAILEDRESERVATION(fk_transactionid,debitreservationid,reservationtime,reservationamount,currencycode,availableamount) 
    BULK COLLECT 
    INTO p_failedreservation                                                                                                     
    FROM failedreservation;

    -- This is line 27
    SELECT frs.debitreservationid
    INTO vdbtid_coll
    FROM TABLE(p_failedreservation) frs;


    FOR v_iterate IN vdbtid_coll.FIRST..vdbtid_coll.LAST  
    LOOP
        dbms_output.put_line('The currency code is: '||v_iterate);  
    END LOOP;

END;

Why is the code generating this error ?

Answer 1

You've declared vdbtid_coll as a collection type, so you need to bulk collect into that too:

SELECT frs.debitreservationid
BULK COLLECT INTO vdbtid_coll
FROM TABLE(p_failedreservation) frs;

With that change:

PL/SQL procedure successfully completed.
The currency code is: 1
The currency code is: 2

That's just giving you the index number in the collection though, so I don't think it's what you really want. You may want:

FOR v_iterate IN vdbtid_coll.FIRST..vdbtid_coll.LAST  
LOOP
    dbms_output.put_line('The currency code is: '
        || p_failedreservation(v_iterate).currencycode);  
END LOOP;

which gets:

PL/SQL procedure successfully completed.
The currency code is: USD
The currency code is: NOK

You don't really need the second select/collection at all though, you can do:

FOR v_iterate IN 1..p_failedreservation.COUNT
LOOP
    dbms_output.put_line('The currency code is: '
        || p_failedreservation(v_iterate).currencycode);  
END LOOP;

... for the same result. Although I'm not sure what the relevance of the debitreservationid is in that second query, as it is the same value (1) in both rows.