Help with Script to convert Long to Clob
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Help with Script to convert Long to Clob

  1. #1
    Join Date
    Sep 2000
    Posts
    3

    Angry Help with script to convert long to clob

    HELP???

    Background : I have a large table containing a Long field. Replication wont rep a long field so I have to convert it to a clob. Oracle provides a to_lob function to copy data from a long to a clob. Clobs can be stored inline or outline. Outline storage uses db_block_size as it's min extent (at a minimum). The to_lob function has a bug that will ALWAYS sotre it outline. That's right. I've verified it after a two week investigation with oracle. They will not help me write a script as a workaround. I have over 1.6 million rows and not nearly enough disk space to do the job all at once.

    The plan is to move 1,000 rows at a time from the original table to a outline clob table. Then move those 1,000 rows to the inline table, truncate the outline table and continue until the original table is done. I can then remove the original table and the interim table and rename the inline clob table to the original. Got it? Ok. I'm not scripter but I've put this together and just can't get any further.

    The protions REM'd out are done so because they will need to exist in the final version of the script ( so I can run it on production). Work tablespace has about 5 G of space.


    Here is what I have. I'm getting an error on the DELCARE?
    DECLARE

    counter integer;

    fetch_cursor_id integer;
    insert_cursor_id integer;
    cursor_exe integer;

    o_tarid number(7);
    o_pcrid number(7);
    o_name varchar2(8);
    o_time date;
    o_pub varchar2(1);
    o_text long;
    o_short_text varchar2(255);

    i_tarid number(7);
    i_pcrid number(7);
    i_name varchar2(8);
    i_time date;
    i_pub varchar2(1);
    i_text clob;
    i_short_text varchar2(255);

    CURSOR logfile_out_cursor IS
    SELECT TARID, PCRID, NAME, TIME, PUB,
    TEXT, SHORT_TEXT FROM LOGFILE;

    CURSOR logfile_in_cursor IS
    SELECT TARID, PCRID, NAME, TIME, PUB,
    TEXT, SHORT_TEXT FROM LOGFILE_2;

    BEGIN

    open logfile_out_cursor;
    open logfile_in_cursor;
    fetch_cursor_id := dbms_sql.open_cursor;
    insert_cursor_id := dbms_sql.open_cursor;

    LOOP

    FETCH logfile_out_cursor into o_tarid, o_pcrid, o_name,
    o_time, o_pub, o_text, o_short_text;
    EXIT WHEN logfile_out_cursor%NOTFOUND;

    dbms_sql.parse(insert_cursor_id, 'INSERT INTO logfile_2 (tarid,
    pcrid, name, time, pub, text, short_text) values (o_tarid,
    o_pcrid, o_name, o_time, o_pub, to_lob(o_text), o_short_text)',
    dbms_sql.native);
    cursor_exe := dbms_sql.execute(insert_cursor_id);

    IF MOD(logfile_out_cursor%ROWCOUNT, 1000) = 0 THEN

    COMMIT;

    LOOP

    FETCH logfile_in_cursor into i_tarid, i_pcrid, i_name,
    i_time, i_pub, i_text, i_short_text;
    EXIT WHEN logfile_in_cursor%NOTFOUND;

    INSERT INTO logfile_3 (tarid, pcrid, name, time, pub,
    text, short_text) values (i_tarid, i_pcrid, i_name,
    i_time, i_pub, i_text, i_short_text);

    END LOOP;

    dbms_sql.parse (fetch_cursor_id, 'truncate table logfile_2',
    dbms_sql.native);
    cursor_exe := dbms_sql.execute(fetch_cursor_id);

    COMMIT;

    END IF;

    END LOOP;

    COMMIT;

    LOOP

    FETCH logfile_in_cursor into i_tarid, i_pcrid, i_name, i_time,
    i_pub, i_text, i_short_text;
    EXIT WHEN logfile_in_cursor%NOTFOUND;

    INSERT INTO logfile_3 (tarid, pcrid, name, time, pub, text,
    short_text) values (i_tarid, i_pcrid, i_name, i_time,
    i_pub, i_text, i_short_text);

    END LOOP;

    COMMIT;

    CLOSE logfile_in_cursor;
    CLOSE logfile_out_cursor;
    dbms_sql.close_cursor(fetch_cursor_id);
    dbms_sql.close_cursor(insert_cursor_id);

    END;
    /



    [Edited by mariano on 09-15-2000 at 03:56 PM]

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    What's the error?

  3. #3
    Join Date
    Sep 2000
    Posts
    3

    Exclamation Error on the Declare?

    I updated the original post. Here is the error :

    DECLARE
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
    ORA-06512: at "SYS.DBMS_SQL", line 32
    ORA-06512: at line 46

    Any help?

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    The cause of ORA-00984, column not allowed here, is the use a column name where it is not allowed, such as in the VALUES clause of an insert statement. So check the statements.

  5. #5
    Join Date
    Sep 2000
    Posts
    3

    Thumbs down This script will not work.

    This script will not work.
    Select from long field will truncate after about 32k bytes.

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    SELECT long_column
    INTO long_plsql_variable
    FROM long_table
    truncates the long after about 32k, the maximum size of long and varchar2 pl/sql variable.
    INSERT INTO lob_table
    SELECT n, TO_LOB(long_column)
    FROM long_table
    inserts the complete long column into the lob column.


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