PeopleSoft :: Insert into Table using Select statement(illegal use of long datatype Error)

This note explains the inability to perform SQL: INSERT INTO...SELECT FROM when columns inserted/selected are of type LONG.
In this case we have multiple option to solve the issue,here we are refering to the PL/SQl using the cursor method to overcome this issue.
There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:
TO_LOB;
PL/SQL;
DBMS_XMLGEN;
Dictionary Long Application (oracle-developer.net utility).


Error::ORA-00997: illegal use of LONG datatype error

INSERT INTO TABLE_B

 (

ROW_ID,

LONG_COLUMN

)

SELECT ROW_ID,

LONG_COLUMN

FROM TABLE_A;

 COMMIT;

Where::

TABLE_A has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN (LONG)

TABLE_B has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN2 (LONG)

Solution

DECLARE

 CURSOR c IS

 SELECT ROW_ID, LONG_COLUMN

 FROM TABLE_A;

 rc c%ROWTYPE;

 BEGIN

 OPEN c;

 LOOP

 FETCH c INTO rc;

 EXIT WHEN c%NOTFOUND;

 INSERT INTO TABLE_B

 (

 ROW_ID,

 LONG_COLUMN2

 )

 VALUES

 (

 rc.ROW_ID,

 rc.LONG_COLUMN

 );

 END LOOP;

 COMMIT;

 END;