ORA-00997: illegal use of LONG datatype

Posted on 2008-10-09
Medium Priority
Last Modified: 2013-12-11
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%';
Question by:m_latha
LVL 74

Accepted Solution

sdstuber earned 1500 total points
ID: 22680286
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
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22680312
What is the datatype of doc_document?

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

Open in new window


Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question