Re: Working with BLOBS

Jeremy <newspostings_at_hazelweb.co.uk> wrote in message news:<MPG.1a1aa7b83e92bb3a98993b_at_news.individual.net>...
>
> 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_at_yahoo.com.au.nospam Received on Tue Nov 11 2003 - 05:04:35 CST