I tried a straight forward ALTER TABLE and ended up with an error SQL alter table test 2 modify (value clob); modify (value clob) * ERROR at line 2: ORA-22858: invalid alteration of datatype The SQL ref doesn't mention anything about converting VARCHAR2 into CLOB. Also, if I have stored procedures that use this column (insert/update/delete/query) how would changing it to CLOB affect these? oerr ora 22858 22858, 00000, "invalid alteration of datatype" // *Cause: An attempt was made to modify the column type to object, REF, // nested table, VARRAY or LOB type.
Would they need a re-write to work with dbms_lob instead? S:- Cannot believe I actually got a chance to Submit a question to you. ) (but it requires a copy of the table be made under the covers) Note: The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. // *Action: Create a new column of the desired type and copy the current // column data to the new type using the appropriate type // constructor.
The bad: If I then run the PL/SQL again, I get an email with an empty PDF.It sort of feels like the highlighted select statement (above) isn't finding anything to actually select. A simple select statement shouldn't require a lock on the row.Consider the following PL/SQL code, which works just fine ONCE after uploading a CLOB.The good: Immediately after I upload the file and run the PL/SQL, I get an email with a PDF that looks perfect - it contains the correct fields, background watermark, and so forth.Answer: Oracle provides several ways to convert a varchar column t a clob: 1 - use the to_clob built-in function: The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.alter table t add y_copy varchar2(4000); update t set y_copy = y; update t set y = null; commit; alter table t modify y long; alter table t modify y clob; update t set y = y_copy; alter table t drop column y_copy; You need to have a commit after "update t set y = y_copy;" Otherwise you loose all the data, because the next statement drops the column and you will never able to recover.alter table t add y_copy varchar2(4000); update t set y_copy = y; update t set y = null; commit; alter table t modify y long; alter table t modify y clob; update t set y = y_copy; alter table t drop column y_copy; Correct Version as follows: alter table t add y_copy varchar2(4000); update t set y_copy = y; ' Commit is here itself is not going to harm.Then next approach came to me was to spilt this and commit each after 1 M record by using PL/SQL block.I dont want to do this as well.] My database is 11G. September 23, 2013 - pm UTC use create table as select, create a new table - you are doing to update every row, you will lock the entire table, instead of creating gobs of redo and undo - just to a nice big create table as selelct create table new select .....Your extra commit I don't like Hi Tom I have one huge table with 400 M records.