• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3961
  • Last Modified:

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%';
0
m_latha
Asked:
m_latha
1 Solution
 
sdstuberCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now