Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to convert LONG data type to CLOB
Anjana
post Nov 15 2007, 02:24 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 15-November 07
Member No.: 14,019



Hi,

i am trying insert data from one DB to other DB table.
one field data type is LONG in first DB table, Same field data type in other DB is CLOB.
i used TO_LOB function to convert from LONG to CLOB data type.

My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.

Give suggesition to reslove this issue.

Thanks & Regards
---------------------
Anjana
Go to the top of the page
 
+Quote Post
Starikovsky
post Nov 15 2007, 07:24 AM
Post #2


Advanced Member
***

Group: Members
Posts: 119
Joined: 13-November 07
Member No.: 13,939



Are you perhaps doing a CREATE TABLE AS SELECT kind of operation?

We have used TO_LOB function to convert a zillion LONG columns with no issues at all.

I would suggest to review documentation one more time...
http://download-west.oracle.com/docs/cd/B1...39a.htm#1189967

If it fails again please post relevant code as well as complete error stack.
Go to the top of the page
 
+Quote Post
HAL9000
post Nov 15 2007, 07:32 AM
Post #3


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



Anjana,

http://www.dba-village.com/village/dvp_tip...ils?TipIdA=2490

Using TO_LOB one can easily convert LONGS to LOBS.



Using the same conversion function one can convert LONG into CLOB and LONGRAW TO BLOB.



Have a look into following example



SQL>create table tlong(itemcd number(30),itemdesc long);

/

Table created.



SQL>Create table tlob(ItemCd Number(30),Itemdesc clob);

Table created



Now dump some values from some table into table tlong

SQL>insert into tlong select icode,iname from InvTab;

2000 rows created.

Now try to insert into lob table from long table

SQL>Insert into tlob select itemcd,TO_LOB(itemdesc) from tlong

2000 rows created.



but in PL/SQL you need to handle different way

normal select into insert will not work,but excute immediate will be the workaround in oracle 8.1.7



Let's see how it can be done PL/SQL



begin

insert into tlob select itemcd,TO_LOB(itemdesc) from tlong;

end;

/



the above pl/sql block works well with oralce 9i and oracle 10g but fails in oracle 8.1.7 with the following error

ERROR at line 2:

ORA-06550: line 2, column 33:

Please-00201: identifier 'TO_LOB' must be declared

ORA-06550: line 2, column 1:

PL/SQL: SQL Statement ignored



Then you should use dynamic sql (Execute Immediate 'SQL statement') as follows



begin

execute immediate 'insert into tlob select itemcd,TO_LOB(itemdesc) from tlong';

end;

/
Go to the top of the page
 
+Quote Post
jkotan
post Nov 15 2010, 05:43 PM
Post #4


Newbie
*

Group: Members
Posts: 2
Joined: 6-October 04
From: Omaha, Nebraska, USA
Member No.: 769



MUCH easier way to do it (in 10gR2, that is):
>> alter table OWNER.TABLE_NAME modify (LONG_COLUMN_NAME CLOB) LOB (LONG_COLUMN_NAME) STORE AS (ENABLE STORAGE IN ROW) ;
I just did that on 34 tables... (Thank you, Oracle Developers, for making it easier)



Jim Kotan rolleyes.gif cool.gif
BHY, BHA
Chief Architectural Consultant
Kotan Consulting Services, LLC
USA
Jim (at) KotanConsulting (dot) com


QUOTE (Anjana @ Nov 15 2007, 02:24 AM) *
Hi,

i am trying insert data from one DB to other DB table.
one field data type is LONG in first DB table, Same field data type in other DB is CLOB.
i used TO_LOB function to convert from LONG to CLOB data type.

My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.

Give suggesition to reslove this issue.

Thanks & Regards
---------------------
Anjana
cool.gif cool.gif


--------------------
--
Jim Kotan
President
KCS System Solutions
2252 N. 128 Circle
Omaha, NE 68164
402-706-2150 (Cell)
920-273-6924 (Fax)
Send Text Messages HERE:
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 31st October 2014 - 07:48 PM