oracle PL/SQL：ORA-00932：不一致的数据类型：预期的 UDT 得到 NUMBER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35842619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
提问by User456898
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.
我正在执行 PL/SQL 代码以显示来自 Failed Reservation 表的货币代码。使用对象类型和嵌套表集合。
运行PL/SQL代码时，会产生如下错误。相应的行在 PL/SQL 代码部分突出显示。
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:
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");
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:
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:
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;
Why is the code generating this error ?
为什么代码会产生这个错误？
回答by Alex Poole
You've declared vdbtid_collas a collection type, so you need to bulk collect into that too:
您已声明vdbtid_coll为集合类型，因此您也需要批量收集：
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 debitreservationidis in that second query, as it is the same value (1) in both rows.
......为了同样的结果。虽然我不确定debitreservationid第二个查询中的相关性是什么，因为它在两行中的值相同 (1)。
回答by MT0
You are trying to select multiple rows into a collection. You need to use BULK COLLECT INTOrather than just INTO.
您正在尝试将多行选择到一个集合中。您需要使用BULK COLLECT INTO而不仅仅是INTO.
Change
改变
SELECT frs.debitreservationid
INTO vdbtid_coll
FROM TABLE(p_failedreservation) frs;
To
到
SELECT frs.debitreservationid
BULK COLLECT INTO vdbtid_coll
FROM TABLE(p_failedreservation) frs;
and you probably want the output to be:
你可能希望输出是：
FOR v_iterate IN vdbtid_coll.FIRST..vdbtid_coll.LAST LOOP
dbms_output.put_line('The currency code is: '|| vdbtid_coll(v_iterate) );
END LOOP;
However, you could simplify it all to:
但是，您可以将其全部简化为：
DECLARE
P_FAILEDRESERVATION APPDATA.TYPE_FAILEDRESERVATION_COLL;
BEGIN
SELECT TYPE_FAILEDRESERVATION(
fk_transactionid,
debitreservationid,
reservationtime,
reservationamount,
currencycode,
availableamount
)
BULK COLLECT INTO p_failedreservation
FROM failedreservation;
FOR v_iterate IN p_failedreservation.FIRST .. p_failedreservation.LAST LOOP
dbms_output.put_line(
'The currency code is: '|| p_failedreservation(v_iterate).currencycode
);
END LOOP;
END;
回答by K Rao
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR
PL/SQL：ORA-00932：不一致的数据类型：预期的 UDT 得到 CHAR
You may get this error in case you write type table in place of type object when using BULK COLLECT INTO, i faced this error coz i did so.
如果您在使用 BULK COLLECT INTO 时编写类型表代替类型对象，您可能会收到此错误，我遇到了此错误，因为我这样做了。
FOR EXAMPLE:
例如：
CREATE OR REPLACE TYPE OBJ_TYPE AS OBJECT (NAME VARCHAR2(20));
/
CREATE OR REPLACE TYPE TBL_TYPE IS TABLE OF OBJ_TYPE;
/
CREATE OR REPLACE FUNCTION FUNC1 RETURN TBL_TYPE AS
TBL_TEXT TBL_TYPE := TBL_TYPE();
BEGIN
SELECT ***OBJ_TYPE*** (NAME) BULK COLLECT INTO ***TBL_TEXT*** FROM <table-name> ;
RETURN ***TBL_TEXT***;
END;
/
Care should be taken when using object type and table type in the function.
在函数中使用对象类型和表类型时要小心。
回答by San
Try casting the type array to its type as follows
尝试将类型数组转换为其类型，如下所示
SELECT frs.debitreservationid
BULK COLLECT INTO vdbtid_coll
FROM TABLE(CAST(p_failedreservation as APPDATA.TYPE_FAILEDRESERVATION_COLL)) frs;
