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

Question

我正在执行 PL/SQL 代码以显示失败预订表中的货币代码。使用对象类型和嵌套表集合。
运行 PL/SQL 代码时，会生成以下错误。相应的行在 PL/SQL 代码部分中突出显示。

错误报告:

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:

代码贴在下面:

DDL - 表创建:

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");

对象类型:

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);

嵌套表:

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 代码:

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;

为什么代码会生成此错误？

Answer 1

您已将 vdbtid_coll 声明为集合类型，因此您也需要将其批量收集:

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

有了这个改变:

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

不过，这只是为您提供集合中的索引号，所以我认为这不是您真正想要的。你可能想要:

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;

得到:

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

虽然你根本不需要第二个选择/集合，你可以这样做:

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

...相同的结果。虽然我不确定 debitreservationid 在第二个查询中的相关性是什么，因为它在两行中的值相同 (1)。