How to Insert values into table using select, with a LONG datatype?

Hello, everybody,

I am inserting data to a table from another table(in another DB)with a column as LONG datatype.

First, I used "create table table1 as select * from table2@DBLINK", one column in table2 is LONG datatype, I got the error message of ora-00997: illegal use of long datatype.
Second, I created the empty table1 first, and defined the column as CLOB datatype, then, I used the command "insert into table1 select * from table2@DBLINK", I still got the error message of "illegal use of long datatype".

Could anyone tell me how to slove the problem? Thank you very much.

Regards,

Gary

You can't create and populate a table with a "LONG" datatype that way.

1. Create the new table with a script ...

2. Populate with script that reads into cursor the original table
with the "LONG" datatype and inserts into the new table:

/** CUSTOMER_BINARY **/

DECLARE
V_cust_order_id VARCHAR2(15);
V_cust_order_line_no NUMBER(38,0);
v_cust_line_id VARCHAR2(15);
v_TYPE CHAR(1);
v_BITS LONG RAW;
v_BITS_LENGTH NUMBER;
v_error number;
v_looknow varchar2(4000);

CURSOR GET_customer IS
SELECT
cust_ORDER_ID,
cust_ORDER_LINE_NO,
TYPE,
BITS,
BITS_LENGTH
FROM cust_line_BINARY;

BEGIN

OPEN GET_customer;
FETCH GET_customer INTO
v_CUST_order_ID,
v_cust_order_line_no,
v_TYPE,
v_BITS,
V_BITS_LENGTH;
LOOP
EXIT WHEN GET_customer%NOTFOUND;

begin
insert into T_CUST_line_binary (
CUST_order_id,cust_order_line_no,type,bits,bits_length )
values (v_CUST_order_ID
,v_cust_order_line_no
,V_TYPE
,v_BITS
,v_BITS_LENGTH );
exception when others then
v_error := 1;
end;

FETCH GET_customer INTO
v_CUST_order_ID,
v_CUST_order_line_no,
v_TYPE,
v_BITS,
V_BITS_LENGTH;
END LOOP;
CLOSE GET_customer;
COMMIT;
END;
/


Hope this helps
Gregg