?
Solved

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

Posted on 2010-09-01
6
Medium Priority
?
1,889 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 1000 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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 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