ORA-00997: illegal use of LONG datatype

When i try to retrive the data from Doc_Document using the below query i am getting an error ORA-00997: illegal use of LONG datatype.
select utl_raw.cast_to_varchar2( dbms_lob.substr( doc_document, 2000, 1 ) )
from LN_FIN.eul5_documents
where doc_name like '%Captiva%';
m_lathaAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
either doc_document or doc_name is a LONG type, or possibly both

you can't apply functions (one exception, below) to LONG's in SQL nor can you use them in a where clause

you'll have to use pl/sql or use the TO_LOB function to convert your LONGs to CLOBs and insert them to a different table
0
 
slightwv (䄆 Netminder) Commented:
What is the datatype of doc_document?

The following snippet works in 10.2.0.3
drop table tab1;
create table tab1 ( col1 BLOB );
 
insert into tab1 values('48656C6C6F');
commit;
 
select utl_raw.cast_to_varchar2(dbms_lob.substr(col1,5,1)) from tab1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.