Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Question Unanswered: Illegal use of LONGs

    All,

    I'm trying to do an insert from an old db table to a new one for the purpose of moving data from one to the other. The column in question is a LONG and I'm having problems with the insert in select. I read up on LONGs and selects and I thought I was using it correctly. Here's my query:

    SQL> INSERT INTO jM(body) SELECT t.body FROM jM_temp t, jM m where t.messageID=m.messageID;

    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    Any ideas on how to do this correctly?

    Thanks in advance,
    --Bill

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do this thru PL/SQL... As you see, long columns are not allowed in
    insert into ... select ... from ... nor are they allowed in
    create table .... as select .... from ....

    ie,

    declare
    v_body long;
    v_message number;

    cursor get_long is
    select message_id,
    body
    from table_a;

    begin
    open get_long;
    fetch get_long into v_message,v_body;
    loop
    exit when get_long%notfound;
    insert into table_b values (v_message, v_body);
    fetch get_long into v_message,v_body;
    end loop;

    end;
    /

  3. #3
    Join Date
    Jan 2003
    Posts
    4
    Fantastic, that's really close to what I'm looking for. I was reading that there's a 32k limit on the size of a pl/sql long, so it was suggested to me that for a long > 32k, I loop and fill up the new LONG record.. any idea how to do that?

    Thanks,
    --Bill

    Originally posted by gbrabham
    Do this thru PL/SQL... As you see, long columns are not allowed in
    insert into ... select ... from ... nor are they allowed in
    create table .... as select .... from ....

    ie,

    declare
    v_body long;
    v_message number;

    cursor get_long is
    select message_id,
    body
    from table_a;

    begin
    open get_long;
    fetch get_long into v_message,v_body;
    loop
    exit when get_long%notfound;
    insert into table_b values (v_message, v_body);
    fetch get_long into v_message,v_body;
    end loop;

    end;
    /

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by mcbain
    Fantastic, that's really close to what I'm looking for. I was reading that there's a 32k limit on the size of a pl/sql long, so it was suggested to me that for a long > 32k, I loop and fill up the new LONG record.. any idea how to do that?

    Thanks,
    --Bill
    See this link:

    http://osi.oracle.com/~tkyte/Misc/MoveLongs.html

  5. #5
    Join Date
    Jan 2003
    Posts
    4
    Originally posted by andrewst
    See this link:

    http://osi.oracle.com/~tkyte/Misc/MoveLongs.html
    Cool, thanks for that link. One problem tho - in his script he has:

    SQL> copy from tkyte/tkyte@aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;

    Is there any way to get the 'tkyte/tkyte@aria' programatically from one of the data dictionary tables (or some other way)?

    Thanks,
    --Bill

Posting Permissions

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