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

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

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
vishal_singh
Asked:
vishal_singh
2 Solutions
 
gatorvipCommented:
Out of curiosity - it seems like that column contains text, so why not use CLOB?
0
 
sdstuberCommented:
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!

 
sdstuberCommented:
Here is a simple test case to demonstrate the problem
ee.txt
0
 
sdstuberCommented:
I suggest export/import to copy the table from the SYSADM schema to your own.

Easy, and it works regardless of column size
0
 
vishal_singhAuthor Commented:
Thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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