Solved

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

Posted on 2010-09-01
6
1,874 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
[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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 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