Results 1 to 12 of 12
  1. #1
    Join Date
    May 2002
    Posts
    7

    Unhappy Unanswered: How to Copy a Table With a LONG RAW Column

    Hello:

    Can anyone tell me how to copy a table with a Long Raw column into another table with the same structure using PL/SQL?
    Any help is appeciated.

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Use COPY command to get this done:

    copy to user/pass@db_link insert new_table(col1, col2,, ...) using select col1, col2, ... from old_table;

    One limitation is that data more than 64K (65535) characters will be truncated.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  3. #3
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    31
    And where the data is more than 64k?
    My homepage:
    http://www.buro9.com/
    My work:
    http://www.btopenworld.com/
    http://www.officialfootballsites.co.uk/
    http://www.jeepster.co.uk/

  4. #4
    Join Date
    May 2002
    Posts
    7
    Thanks for the feedback.

    Unfortunately the data in the Long Raw Columns are more than 64K.
    Is there a way to copy the Long Rwas into a Blob column, preferrably using Pl/SQL?

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Originally posted by buro9
    And where the data is more than 64k?
    In LONG RAW row from this table. Each row characters stored in LONG RAW doesn't have to exeed 64K or 65535 characters.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Originally posted by Harold Hunsaker
    Thanks for the feedback.

    Unfortunately the data in the Long Raw Columns are more than 64K.
    Is there a way to copy the Long Rwas into a Blob column, preferrably using Pl/SQL?
    Sure, there is a way. Do something like that:

    - Create table clob_table with the same structure as long_raw_table:

    CREATE TABLE clob_table (...,...,..., CLOB_COLUMN CLOB);

    - Use an INSERT ... SELECT statement to copy the data in all rows for the LONG column into the newly created LOB column:

    INSERT INTO clob_table (...,...,..., CLOB_COLUMN)
    SELECT ...,...,..., TO_LOB(LONG_RAW_COLUMN) FROM long_raw_table;


    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  7. #7
    Join Date
    May 2002
    Posts
    7
    The bulk insert is what I wanted to use. Unfortunately, we are using Oracle 8.05 which does not support the TO_LOB function, nor the UTL_RAW package. I am willing to try the SQL Plus COPY, do you know if it works with Long Raw columns that are > 64K?

    Thanks again for your help.

  8. #8
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Yes, it works but the data beyond 64K will be truncated.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  9. #9
    Join Date
    May 2002
    Posts
    7
    Is it possible to convert a Long Raw column > 64 K to a blob? I see that there is a DBMS_LOB package, and it has a function that will read a BFILE into a Lob column. I was wondering if the Long Raw could be stored in a BFILE and read into a Lob column? Do you know if this is possible?

    Thanks again.

  10. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Yes. It is 100% possible. It will require some PL/SQL coding, of course.

    If you like you can write it yourself, if looking for already working scripts, then take a look at those How to copy LONG to CLOB ( > 64K) solutions. They should get you the idea.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  11. #11
    Join Date
    Jan 2003
    Posts
    1
    Originally posted by clio_usa
    Use COPY command to get this done:

    copy to user/pass@db_link insert new_table(col1, col2,, ...) using select col1, col2, ... from old_table;

    One limitation is that data more than 64K (65535) characters will be truncated.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .
    I tried your method and here is the error I received:

    insert into z_sig_clob(certnum, image) select certnum, image from sigimage
    *
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    I am using Oracle 8.15.

    I also tried the copy command but that did not work either. The error information follows:

    Array fetch/bind size is 15. (arraysize is 15)
    Will commit when done. (copycommit is 0)
    Maximum long size is 80. (long is 80)

    select certnum, image from sigimage
    *
    Error in SELECT statement: ORA--20289273: Message -20289273 not found; product=RDBMS; facility=ORA


    How come? ???????

    Matt

  12. #12
    Join Date
    Jan 2008
    Posts
    1

    Copy long raw data between tables

    create table tmp_src (x long raw);
    create table tmp_dst (x long raw);
    insert into tmp_src values (HEXTORAW('AABBCC'));

    declare
    cursor cur is
    select x from tmp_src;
    begin
    FOR rec in cur LOOP
    insert into tmp_dst values (rec.x);
    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
  •