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

[gary]

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

[gbrabham]

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