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

    Post Unanswered: During Insert: Long to Varchar2(4000) conversion

    Can anyone help me out with the sytax?
    I keep getting the following error: "ORA-00997: illegal use of LONG datatype"
    I'm trying to insert many rows (from a query) which includes 2 Long fields inserting into 2 VARCHAR2 fields.

    As such:

    INSERT INTO MyTable
    (XX, YY, ZZ, VARCHAR2, AA, VARCHAR2, BB)

    SELECT X,Y,Z, LONG, A, LONG_AGAIN, B
    FROM TABLE Result, Person
    WHERE Person.ID = Result.PersonID

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    LONG has been deprecated ages ago.
    You should use the CLOB datatype which does not have those restrictions.

  3. #3
    Join Date
    Apr 2004
    Posts
    5

    Wink

    I know, this is why I'm trying to take data OUT of the LONG, not into.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could write a PL/SQL program something like:

    Code:
    begin
       for p in (select * from person)
       loop
          for r in (select * from result where result.personid = person.id)
          loop
             insert into mytable (x, y, z, a, b)
             values (p.x, p.y, r.z, p.longval, r.longval);
          end loop;
       end loop;
    end;
    However, this is only good if your LONG columns contain no more than 32K of data. If there is more than 32K than you need to use Pro*C to get it in chunks, but I'm afraid I don't know the details for that.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by HockeyNut
    I know, this is why I'm trying to take data OUT of the LONG, not into.
    Why not simply change the data type of the columns to CLOB then?
    The following should work:
    Code:
    ALTER TABLE mytable MODIFY thecolumn CLOB;

  6. #6
    Join Date
    Apr 2004
    Posts
    5
    It's from a database used by a 3rd party application, so we can't alter its structure I'm afraid.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by HockeyNut
    It's from a database used by a 3rd party application, so we can't alter its structure I'm afraid.
    Depends on the application. An application using e.g. a recent JDBC might not even notice the difference. But that highly depends on the software and on the driver used.

    Assuming you can't even test that, I would suggest to split your statement into two. First do a INSERT ... SELECT with the first column.
    Then run an UPDATE for the second column.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Look up the to_lob function, which will convert a long to a lob.

    for example

    Code:
    insert into my_table(my_clob_column)
    select to_lob(my_long_column)
    from my_other_table;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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