OraFAQ Forum: SQL & PL/SQL » How do I replace characters in a CLOB data type

Home » SQL & PL/SQL » SQL & PL/SQL » How do I replace characters in a CLOB data type

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

How do I replace characters in a CLOB data type [message #19371]

Wed, 13 March 2002 08:08

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: How do I replace characters in a CLOB data type [message #19372 is a reply to message #19371]

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

Previous Topic:	Re: Oracle 7.3 Installation
Next Topic:	EXP-00046

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 13:03:08 CDT 2024