Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1919
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
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

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