I have problem executing below stored procedure.
I am getting error of ORA-00932: inconsistent datatypes: expected - got BLOB
when I add FM.FAXFILE_BLOB
column in below stored procedure.FAXFILE_BLOB
is a blob field.
If I remove this field everything works fine.I don't know why this is happening.
Please help....
我在下面的存储过程中执行有问题。我收到错误的ORA-00932:不一致的数据类型:预期 - 当我在下面的存储过程中添加FM.FAXFILE_BLOB列时得到BLOB.FAXFILE_BLOB是一个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;
/
2 个解决方案
#1
11
You cannot specify DISTINCT if the select_list contains LOB columns.
如果select_list包含LOB列,则不能指定DISTINCT。
Try to use a scalar subquery to get the BLOB field.
尝试使用标量子查询来获取BLOB字段。
#2
2
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.
set运算符在BLOB,CLOB,BFILE,VARRAY或嵌套表类型的列上无效。
The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
UNION,INTERSECT和MINUS运算符在LONG列上无效。
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.
如果set运算符前面的选择列表包含表达式,则必须为表达式提供列别名,以便在order_by_clause中引用它。
You cannot also specify the for_update_clause with the set operators.
您也不能使用set运算符指定for_update_clause。
You cannot specify the order_by_clause in the subquery of these operators.
您不能在这些运算符的子查询中指定order_by_clause。
You cannot use these operators in SELECT statements containing TABLE collection expressions.
您不能在包含TABLE集合表达式的SELECT语句中使用这些运算符。
Reference doc.
参考文档。
#1
11
You cannot specify DISTINCT if the select_list contains LOB columns.
如果select_list包含LOB列,则不能指定DISTINCT。
Try to use a scalar subquery to get the BLOB field.
尝试使用标量子查询来获取BLOB字段。
#2
2
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.
set运算符在BLOB,CLOB,BFILE,VARRAY或嵌套表类型的列上无效。
The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
UNION,INTERSECT和MINUS运算符在LONG列上无效。
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.
如果set运算符前面的选择列表包含表达式,则必须为表达式提供列别名,以便在order_by_clause中引用它。
You cannot also specify the for_update_clause with the set operators.
您也不能使用set运算符指定for_update_clause。
You cannot specify the order_by_clause in the subquery of these operators.
您不能在这些运算符的子查询中指定order_by_clause。
You cannot use these operators in SELECT statements containing TABLE collection expressions.
您不能在包含TABLE集合表达式的SELECT语句中使用这些运算符。
Reference doc.
参考文档。