-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|