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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
gatorvip
Out of curiosity - it seems like that column contains text, so why not use CLOB?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suggest export/import to copy the table from the SYSADM schema to your own.
Easy, and it works regardless of column size
Easy, and it works regardless of column size
ASKER
Thanks