Re: converting long to lob - the easiest and fastest way ?
create table newtab select no, to_lob(col1) col1 from table1; it's better to create the table first and then use insert +append, that way you control better which datatypes your columns will have. 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
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
RE: converting long to lob - the easiest and fastest way ?
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
RE: converting long to lob - the easiest and fastest way ?
One more thing my database is 8.1.7, think this alter table modify from long to clob is 9i feature 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 Siddharth Haldankar Sent: Thursday, September 11, 2003 3:44 PM To: Multiple recipients of list ORACLE-L 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
RE: converting long to lob - the easiest and fastest way ?
Example given below is for the same user. /home/govindank> sqlplus user/[EMAIL PROTECTED] SQL*Plus: Release 8.1.6.0.0 - Production (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production SQL> create table my_long_table (empno number ,emphist long); Table created. SQL> create table my_clob_table(empno number , emphist clob); Table created. SQL> insert into my_long_table values(1, 'Name for Emp no 1'); 1 row created. SQL> set copycommit 1; SQL> set arraysize 1000; SQL> quit /home/govindank> exp user/[EMAIL PROTECTED] tables=my_long_table file=my_long_table.dmp log=my_long_table_exp.log Export: Release 8.1.6.3.0 - Production on (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set About to export specified tables via Conventional Path ... . . exporting table MY_LONG_TABLE 1 rows exported Export terminated successfully without warnings. /home/govindank> sqlplus user/[EMAIL PROTECTED] SQL*Plus: Release 8.1.6.0.0 - Production on (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production SQL> rename my_long_table to my_long_table_old; Table renamed. SQL> rename my_clob_table to my_long_table; Table renamed. SQL> quit Disconnected from Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production /home/govindank> imp user/[EMAIL PROTECTED] file=my_long_table.dmp log=my_clob_table_imp.log ignore=Y Import: Release 8.1.6.3.0 - Production on (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production Export file created by EXPORT:V08.01.06 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set . importing TOMX's objects into TOMX . . importing table"MY_LONG_TABLE" 1 rows imported Import terminated successfully without warnings. /home/govindank> sqlplus user/[EMAIL PROTECTED] SQL*Plus: Release 8.1.6.0.0 - Production on (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production With the Partitioning option JServer Release 8.1.6.3.0 - Production SQL> rename my_long_table to my_clob_table; Table renamed. SQL> rename my_long_table_old to my_long_table; Table renamed. SQL> desc my_long_table; Name Null?Type - EMPNO NUMBER EMPHISTLONG SQL> desc my_clob_table; Name Null?Type - EMPNO NUMBER EMPHISTCLOB SQL> set long 1; SQL> select * from my_clob_table; EMPNO EMPHIST 1 Name for Emp no 1 SQL> HTH GovindanK On Thu, 11 Sep 2003 02:39 , Siddharth Haldankar <[EMAIL PROTECTED]> sent: > >One more thing my database is 8.1.7, think this alter table >modify from long to clob is 9i feature > >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 Siddharth Haldankar > >Sent: Thursday, September 11, 2003 >3:44 PM > >To: Multiple recipients of list >ORACLE-L > >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 > > Message sent via Zuvio Mai
