Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Unhappy Unanswered: insert using long datatype

    Hello there !

    I have table t_prod in production database and its structure is as follows.

    DESC t_prod

    d_id NUMBER;
    d_name VARCHAR2(100);
    d_desc LONG;

    The table t_test is having the same structure.

    DESC t_test

    d_id NUMBER;
    d_name VARCHAR2(100);
    d_desc LONG;

    When i try to insert the data from t_prod, i get this error


    SQL> INSERT INTO IE_EXPORT_DEVICE
    2 SELECT * FROM MW_TEST.IE_EXPORT_DEVICE@CRMU_LINK;
    SELECT * FROM MW_TEST.IE_EXPORT_DEVICE@CRMU_LINK
    *
    ERROR at line 2:
    ORA-00997: illegal use of LONG datatype

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Cause ~~~~~~~ Long columns cannot be used with the DISTINCT operator in SELECT statements. They cannot be referenced when creating a table with query (CREATE TABLE...AS SELECT...) or when inserting into a table with a query (INSERT INTO ... SELECT..). These are restrictions of the long datatype.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    22
    Thanks for the info !

    But now how would i copy the data from the table having LONG datatype to another table of the same structure ???

    Help !!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    go to http://asktom.oracle.com & do your own research!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2004
    Posts
    22
    I GAVE A SHOT... AND THIS ONE WORKED FOR ME !!!

    DECLARE
    CURSOR c1 IS
    SELECT * FROM MW_TEST.IE_EXPORT_DEVICE@CRMU_LINK;
    BEGIN
    FOR c1_rec IN c1 LOOP
    INSERT INTO IE_EXPORT_DEVICE
    VALUES (c1_rec.DEVICE_ID,c1_rec.DEVICE_NAME,c1_rec.DEVICE _DESCRIPTION);
    END LOOP;
    COMMIT;
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •