|
Category :
Database
Resources -> Database Administration -> Troubleshooting & Solutions
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
Ever run in to this error
: ORA-00997:
illegal use of LONG datatype
when doing an insert with select against tables with LONG datatype
columns?
The above error is due to a know
restriction where you cannot do an insert with select or Create table
as select involving tables with LONG datatypes. One thing to keep in
mind is the fact that LONG datatypes will not be supported by oracle
in the near future. Make sure that you migrate the columns from LONG
to LOB or CLOB. For now, we can do the data transfer 2 ways : (1) using the COPY
command and (2) Using the dbms_metadata_util package.
(1) An example of doing the data
transfer using the COPY command:
-- You can set long to any large
value as long as it is larger than the max length of any
-- long column in the table. Exception is that sqlplus commands cannot
handle a long
-- greater than 64k. The long column will always be truncated at
64k.
set long 2000000000
-- commit after one batch of recordS
set copycommit 1
-- 100 records per batch
set arraysize 100
-- copy is a SQL*Plus command and needs the continuation character "-"
for multiple lines.
SQL>
copy from REMOTE_USER/REMOTE_USER_PASSWORD@remotedb -
insert testtab using select * from testtab
(2) Using the dbms_metadata_UTIL
package:
This package is used by oracle
internally for its conversion from long to varchar or CLOB datatypes
and we should be able to use this package for our long to varchar or
CLOB conversions. The dbms_metadata_util package is owned by SYS and
the DBA needs to give execute privileges on this package in order to
allow other users to use it. An example on doing the conversion:
(i) If the maximum length of the
long datatype column is less than or equal to 4000 then we can do the
conversion as follows:
SQL> select col1,
dbms_metadata_util.long2varchar(4000,'TABLE_NAME','COLUMN_NAME',rowid)
Column_alias from table_name;
The
dbms_metadata_util.long2varchar function accepts the length of the
LONG column as its first argument followed by the table name, the long
datatype column name and the rowid. Using the above statement as part
of CTAS (create table as select), we get:
SQL> create table test_table
nologging as select col1, dbms_metadata_util.long2varchar(4000,'TABLE_NAME','COLUMN_NAME',rowid)
Column_alias from table_name;
The above statement creates the
table and defines the long column as varchar2(4000) within the new
table. If the maximum length of the long column exceeds 4000 then the
function returns null.
(ii) If the maximum length of the
long column is greater than 4000 then we could use an alternate
function that comes with the dbms_metadata_util package to convert the
long data to CLOB. An example:
SQL> select
dbms_metadata_util.long2clob(40000,TABLE_NAME','COLUMN_NAME',rowid)
Column_alias from table_name;
|
