Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-out.triton.net!triton.net!newsfeeder.triton.net!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews1.google.com!not-for-mail From: wizofoz2k@yahoo.com.au (Noons) Newsgroups: comp.databases.oracle.server Subject: Re: Working with BLOBS Date: 11 Nov 2003 03:04:35 -0800 Organization: http://groups.google.com Lines: 29 Message-ID: <73e20c6c.0311110304.1754b42@posting.google.com> References: <1068497208.914442@yasure> NNTP-Posting-Host: 211.29.136.12 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1068548676 24348 127.0.0.1 (11 Nov 2003 11:04:36 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 11 Nov 2003 11:04:36 +0000 (UTC) Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:247435 Jeremy wrote in message news:... > > OK - let me alter the scenario. We have a table with a CLOB column which > contains an RTF document. Using pl/sql, what is the easiest way to read > the CLOB and replace all occurrences of 'x' with 'y'? > > On 8.1.7 I don't see DBMS_LOB procedures or functions which appear to do > a simple REPLACE - can you help? In short, there is nothing in 8.1.7 that will really help you. The so-called "LOB-replaces-LONG" stuff propagated by Oracle is of relative use only until 9i. Then you can use CLOB with most string functions and DBMS_LOB becomes mostly redundant with CLOB. The only thing I can think of is to create a temp CLOB, use DBMS_LOB.INSTR (which will locate a string, not just a character) and then copy-before to temp CLOB, copy new string to temp and copy-after to temp, finally replacing original CLOB with temp one. You can probably roll all that into your own function, but is it worth it? Upgrade to 9ir2 and be done with the problem. If at all possible. Cheers Nuno Souto wizofoz2k@yahoo.com.au.nospam