Error "ORA-00932: inconsistent datatypes: expected - got BLOB" in Join Statement

Question

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....

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

Solution 1

You cannot specify DISTINCT if the select_list contains LOB columns.

Try to use a scalar subquery to get the BLOB field.

Solution 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.

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.

Reference doc.

Comments

[Rohan]

Thanks. This helped. But why is this so?

[Alessandro Rossi]

There are some restrictions on LOBs. Probably it's because they're stored in a different way from basic data-types. To go deeper on it refer to docs.oracle.com/cd/B28359_01/appdev.111/b28393/… and to docs.oracle.com/cd/B28359_01/appdev.111/b28393/…