Solved

ORA-00997: illegal use of LONG datatype

Posted on 2008-10-09
2
3,872 Views
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%';
0
Comment
Question by:m_latha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22680312
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 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