ORA-00932: inconsistent datatypes: expected LONG BINARY got BLOB

Question

create or replace function CasLengthOfLongRaw( p_tname in varchar2,  
     p_cname in varchar2,  
     p_rowid in rowid ) return number  
AUTHID CURRENT_USER  as pragma autonomous_transaction;  
   l_length number;  
begin 
   execute immediate 
   'insert into TEMPCSSRPLLOB(LOBFIELD)  
   select to_lob(' || p_cname || ')  
   from ' || p_tname || '  
   where rowid = :x' using p_rowid;  
   select dbms_lob.getlength(LOBFIELD) into l_length  
   from TEMPCSSRPLLOB;  
   commit;  
    return  l_length;  
end;

For this Function I am getting

error at Line 7 and ORA Error ORA-00932: inconsistent datatypes: expected LONG BINARY got BLOB ORA-06512: at "CASLENGTHOFLONGRAW", line 7 00932. 00000 - "inconsistent datatypes: expected %s got %s"

A kind advice : Please don't add words like "ASAP", "urgent" etc. I removed it. In S.O, users spend their time to help you voluntarily. — Kaushik Nayak

Alex Poole Alex Poole · Accepted Answer · 2018-05-20T08:57:55

The to_lob() function converts a LONG or LONG RAW value to a LOB.

From the error message your target column, LOBFIELD, is a BLOB, so it is expecting the source column identified by the variables p_tname.p_cname to be data type LONG RAW. Clearly, from the message, it is already a BLOB, so no conversion is needed:

...
   execute immediate 
   'insert into TEMPCSSRPLLOB(LOBFIELD)  
   select ' || p_cname || '  
   from ' || p_tname || '  
   where rowid = :x' using p_rowid;  
...

But as the function is called CasLengthOfLongRaw the problem really seems to be that the function does what you want but you are passing it the details of an unsuitable table and column name for the operation it's suppsoed to perform.

ORA-00932: inconsistent datatypes: expected LONG BINARY got BLOB

1 Answers