RE: converting long to lob - the easiest and fastest way ?

[Siddharth Haldankar]

Thanks Tanel,   But to_lob does not work in update clause, it only works in select clause, that is the restriction it has.   With Warm Regards Siddharth Haldankar Cisco Systems Inc. ODC Zensar Technologies Ltd. #  : 4128374 [EMAIL PROTECTED] [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel Poder Sent: Thursday, September 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: converting long to lob - the easiest and fastest way ?   Sorry, I didn't notice in the end of your post, that you don't want to recreate your table.   Then try this one:   add a new clob type column to your table using alter table add. make an update statement:    update tableA set col1=null, newcol=to_lob(col1);   use alter table drop column col1 afterwards and if needed, rename newcol to col1 using alter table rename column (or whatever the syntax was).   You might want to do this in batches and commit meanwhile if your table is big. Also, if your table is big, then you should test which mode lob segments are faster for your update - nocache nologging or cache logging. Oracle might be able to optimize serial new chunk write requests to batches, but I don't know whether it's true. It depends on the size of your current LONGs as well..   Tanel.   ----- Original Message ----- From: Siddharth Haldankar To: Multiple recipients of list ORACLE-L Sent: Thursday, September 11, 2003 1:14 PM Subject: converting long to lob - the easiest and fastest way ?   Hi Gurus,   How do I convert a long datatype to clob datatype. Say Table A No                    number Col1                 long Would like to convert to Table A No                    number Col1                  clob   Alter table A modify (col1 clob); Gives an error as inconsistent datatypes.   Could you give me an example of this with lob_storage_clause.   I would like to convert the existing data in clob type also. The to_lob works only for select statements, cannot go through that pain of table creation.   Thanks in advance.   - Siddharth