SQL 连接语句中出现错误“ORA-00932：不一致的数据类型：预期 - 得到 BLOB”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9357974/
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
Error "ORA-00932: inconsistent datatypes: expected - got BLOB" in Join Statement
提问by Amol Kolekar
I have problem executing below stored procedure.
I am getting error of ORA-00932: inconsistent datatypes: expected - got BLOBwhen I add
FM.FAXFILE_BLOBcolumn in below stored procedure.FAXFILE_BLOBis a blob field.
If I remove this field everything works fine.I don't know why this is happening.
Please help....
我在执行以下存储过程时遇到问题。当我在下面的存储过程中添加列时
出现错误。是一个 blob 字段。如果我删除此字段，一切正常。我不知道为什么会这样。请帮忙....ORA-00932: inconsistent datatypes: expected - got BLOBFM.FAXFILE_BLOBFAXFILE_BLOB
CREATE OR REPLACE Procedure HCADMIN.Proc_GetFaxDetailsByDate
(
FromDate varchar2 default null,
ToDate varchar2 default null,
FaxNo varchar2 default null,
ClaimNo varchar2 default null,
NspCode varchar2 default null,
PolicyNo varchar2 default null,
HEGICNo varchar2 default null,
cur_faxdetails OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN cur_faxdetails For
Select distinct
FM.RECORDNO_NUM,
FM.CLAIMNO_VAR,
FM.FAXNO_VAR,
FM.FAXSTATUS_VAR,
FM.FAXTYPE_VAR,
FM.USERNAME_VAR,
FM.HEGIC_NO_VAR,
FM.RESEND_NO_NUM,
FM.RESNDCOUNT_NUM,
TO_date(FM.TIMESTAMP_DTE,'dd/MM/yyyy') as "TIMESTAMP_DTE",
FR.RECIPIENTFAXNO_VAR,
FM.FAXFILE_BLOB
From TPA_FAXMASTER FM Left join TPA_FAXRECIPIENT FR on FM.RECORDNO_NUM=FR.RECORDNO_NUM
WHERE
NVL(FM.FAXNO_VAR,'0')=NVL(FaxNo,NVL(FM.FAXNO_VAR,'0')) And
NVL(FR.RECIPIENTFAXNO_VAR,'0')=NVL(FaxNo,NVL(FR.RECIPIENTFAXNO_VAR,'0')) And
NVL(FM.CLAIMNO_VAR,'0')=NVL(ClaimNo,NVL(FM.CLAIMNO_VAR,'0')) And
NVL(FM.NSPID_VAR,'0')=NVL(NspCode,NVL(FM.NSPID_VAR,'0')) And
NVL(FM.POLICYNO_VAR,'0')=NVL(PolicyNo,NVL(FM.POLICYNO_VAR,'0')) And
NVL(FM.HEGIC_NO_VAR,'0')=NVL(HEGICNo,NVL(FM.HEGIC_NO_VAR,'0')) And
(NVL(TO_date(FM.TIMESTAMP_DTE,'dd/MM/yyy'),To_Date('09/09/9999','dd/MM/yyyy'))
BETWEEN NVL (TO_date(FromDate,'dd/MM/yyyy'), NVL(TO_date(FM.TIMESTAMP_DTE,'dd/MM/yyy'),To_Date('09/09/9999','dd/MM/yyyy')))
AND NVL (TO_date(ToDate,'dd/MM/yyyy'), NVL(TO_date(FM.TIMESTAMP_DTE,'dd/MM/yyy'),To_Date('09/09/9999','dd/MM/yyyy'))));
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END;
/
回答by Alessandro Rossi
回答by tharindu_DG
Below restrictions are applied on the set operators.
以下限制适用于集合运算符。
The set operators are subject to the following restrictions:
The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.
You cannot also specify the for_update_clause with the set operators.
You cannot specify the order_by_clause in the subquery of these operators.
You cannot use these operators in SELECT statements containing TABLE collection expressions.
集合运算符受以下限制：
集合运算符对 BLOB、CLOB、BFILE、VARRAY 或嵌套表类型的列无效。
UNION、INTERSECT 和 MINUS 运算符对 LONG 列无效。
如果集合运算符前面的选择列表包含表达式，则必须为该表达式提供列别名，以便在 order_by_clause 中引用它。
您也不能使用集合运算符指定 for_update_clause。
您不能在这些运算符的子查询中指定 order_by_clause。
您不能在包含 TABLE 集合表达式的 SELECT 语句中使用这些运算符。
Reference doc.
参考文档。
