OraFAQ Forum: SQL & PL/SQL » Replacing values in CLOB column

Home » SQL & PL/SQL » SQL & PL/SQL » Replacing values in CLOB column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replacing values in CLOB column [message #38006]

Wed, 13 March 2002 08:10

Mike Oakes
Messages: 25
Registered: December 2000

Junior Member

Hello is it possible for me to replace all instances of "^" with a line feed. I am able to do this with char types but when i try the following command with clob data types, I receive an error "inconsistent datatypes". I tried using TO_CHAR but to no avail. ANy help would be appreciated.
Update Table1 set column1 = replace(COLUMN1,'^',chr(010));
I receive error ORA-00932: inconsistent datatypes. Column1 is a clob data type. Is there any way this can be done.

Report message to a moderator

Re: Replacing values in CLOB column [message #38007 is a reply to message #38006]

Wed, 13 March 2002 08:30

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Use DBMS_LOB.SUBSTR to cast the CLOB to a string:

update table1
   set column1 = replace(dbms_lob.substr(column1), '^', chr(10));

Report message to a moderator

Re: Replacing values in CLOB column [message #38009 is a reply to message #38006]

Wed, 13 March 2002 10:25

Mike Oakes
Messages: 25
Registered: December 2000

Junior Member

They average about 40,000 bytes. I may be hitting the limit.

Report message to a moderator

Re: Replacing values in CLOB column [message #38013 is a reply to message #38006]

Wed, 13 March 2002 11:15

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Yes, dbms_lob.substr can only return up to 4000 bytes for a CLOB since the return type is VARCHAR2.

Report message to a moderator

Previous Topic:	simple PL/SQL question 2
Next Topic:	Insert and Where clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 23:21:28 CDT 2024