Help with Script to convert Long to Clob

[mariano]

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]

[akkerend]

What's the error?

[mariano]

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?

[akkerend]

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.

[mariano]

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

[akkerend]

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.