Using PL/SQL Copy a LONG or LONG RAW into Another Table Fails with ORA-997

출처:  https://metalink.oracle.com/



Using PL/SQL Copy a LONG or LONG RAW into Another Table Fails with ORA-997
----------------------------------------------------------------------------------------------


PROBLEM:
============
You have a table that contains a LONG, and you want to copy it to another table using a CREATE TABLE <new table> AS SELECT * FROM <old table>.  You receive an ora-997 error:

    ORA-00997: illegal use of LONG datatype

Therefore, you cannot issue this command because the table contains a LONG column. This document will explain the steps needed to perform this operation.



SOLUTION:
===========
===========

Disclaimer:
===========
This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support.
The script has been tested and appears to work as intended. However, you should always test any script before relying on it. The way to accomplish the copy of this LONG field into another table can be accomplished by writing a PL/SQL procedure to put the LONG column into a cursor and then put the value into the new table.


STEPS TO PEFORM THIS OPERATION:
======================================
This sample code will only work if the data is limited to 32k in size.  If the LONG size is larger, then OCI or JDBC must be used.

1. Perform a create table as select with the non-long columns
2. Then alter the table and add the long column.
3. Create a short PL/SQL procedure to perform the copy based on the column which identify the record, and run it.


=========
EXAMPLE:
=========

CREATE TABLE use_long (id NUMBER, text LONG);
INSERT INTO use_long VALUES ( 123, 'LONG COLUMN VALUES');

CREATE TABLE use_long2 AS SELECT id FROM use_long;
ALTER TABLE use_long2 ADD (text LONG);

CREATE OR REPLACE PROCEDURE add_long IS
   CURSOR c1 IS SELECT * FROM use_long;
BEGIN
    FOR  i IN c1 LOOP
          IF i.text IS NOT NULL THEN
          UPDATE use_long2
           SET text = i.text
           WHERE use_long2.id = i.id;
       END IF;
END LOOP;
END;
/

EXEC add_long;

SELECT * FROM use_long2;

RELATED REFERENCES:
========================
Note:69627.1 How to Convert LONG Data into LOB Data using PL/SQL
Note:69626.1 How to Convert LONG Data into LOB Data using OCI8
Note:1048555.6 ORA-00997 CREATING TABLE USING 'CREATE TABLE AS SELECT...'
Note 119489.1  How to Copy Data from a Table with a LONG Column into an Existing Table

Oracle7 Server SQL Reference Guide
Oracle7 Server PL/SQL Users Guide and Reference
Oracle8 Server SQL Reference Guide
Oracle8 Server PL/SQL Users Guide and Reference