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?
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)
inserts the complete long column into the lob column.