How to Insert values into table using select, with a LONG datatype?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

Hybrid View

  1. #1
    Join Date
    May 2001
    Location
    Fairfax, virginia
    Posts
    23

    Unhappy 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

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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
  •  



Click Here to Expand Forum to Full Width