Solved

ORA-00997: illegal use of LONG datatype ; unable to insert data into another table

Posted on 2010-09-01
6
1,864 Views
Last Modified: 2012-06-21
Hello Experts,
I have a table containing 100,000 records:
CREATE TABLE SYSADM.PS_COURSE_DESCR
(
  TIMS_COURSE_OWNER  VARCHAR2(5 BYTE),
  COURSE             VARCHAR2(7 BYTE)           NOT NULL,
  COURSE_DESCR_TYPE  VARCHAR2(2 BYTE)           NOT NULL,
  EFFDT              DATE,
  DESCRLONG          LONG
);

I have built another table of the same structire and want to insert those records into another table.

It throws me an error when I run this simple insert statement:
--------------------------------------------------------------------
insert into VSINGH.PS_COURSE_DESCR
select  * from SYSADM.PS_COURSE_DESCR;
---------------------------------------------------------------------
ORA-00997: illegal use of LONG datatype

How do I resolve this issue?

Thanks,
Vishal
0
Comment
Question by:vishal_singh
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 33580263
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 33580502
Out of curiosity - it seems like that column contains text, so why not use CLOB?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33580510
I'm afraid the link above is not reliable.

If your LONG data is greater than 32760 characters then you'll get

ORA-06502: PL/SQL: numeric or value error

when you try to run it.

The reason is  LONG is a distinct subtype of VARCHAR2 in pl/sql,  so when you declare a rowtype for your cursor  your LONG column becomes a LONG variable with the pl/sql limit of 32760 characters and you can't fetch big strings into it.

If your LONG is small enough then it should work


0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 33580540
Here is a simple test case to demonstrate the problem
ee.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33580649
I suggest export/import to copy the table from the SYSADM schema to your own.

Easy, and it works regardless of column size
0
 

Author Closing Comment

by:vishal_singh
ID: 33618644
Thanks
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password_rules_securitty.. 12 36
SQL Query help 3 25
Database Owner 3 21
T-SQL: Please describe what a page split is 5 38
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

730 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