Skip to Main Content
  • Questions
  • LOB - Replacing a part of / Not overwriting

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Nick.

Asked: March 01, 2001 - 5:29 pm UTC

Last updated: June 29, 2022 - 11:07 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Presently, it is not possible to replace a part of a LOB (the DBMS_LOB package does not have a replace function). The write procedure overwrites.

Is it possible, if not why, to write at a certain position (not append) and offset the remaining portion while not overwriting. For example, say that in a CLOB column on a table, I want to replace the string '1234' with the string '123456890' (which is bigger than the one to replaced, and we can't overwrite as this string might be in the middle).

I am curious as to the rationale behind only only appends or overwrites, is it a technical/architectural issue.

Thanks in advance,

Nick

and Tom said...

Well, an overwrite is a replace, what you want is a "global change".

Think of the lob as a big stream of characters like this:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
^

each x represents a database block. Say you wanted to modify the 1234 on the block with the caret ^ under it. That block would have to have all of its data pushed down by 6 bytes. That would push the last 6 bytes off of each subsequent block into the head of the next block. You are rewriting the end of the entire lob -- from that position onwards.

You can do this with dbms_lob.copy - but it will involve rewriting the ENTIRE end of the lob:

ops$tkyte@DEV816> create table t ( x clob );

Table created.

ops$tkyte@DEV816>
ops$tkyte@DEV816>
ops$tkyte@DEV816> create or replace
2 procedure lob_replace( p_lob in out clob,
3 p_what in varchar2,
4 p_with in varchar2 )
5 as
6 n number;
7 begin
8 n := dbms_lob.instr( p_lob, p_what );
9 if ( nvl(n,0) > 0 )
10 then
11 dbms_lob.copy( p_lob,
12 p_lob,
13 dbms_lob.getlength(p_lob),
14 n+length(p_with),
15 n+length(p_what) );
16
17 dbms_lob.write( p_lob, length(p_with), n, p_with );
18 if ( length(p_what) > length(p_with) )
19 then
20 dbms_lob.trim( p_lob,
21 dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
22 end if;
23 end if;
24 end;
25 /

Procedure created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> declare
2 l_lob clob;
3 begin
4 insert into t values ( 'Hello 1234 World' ) returning x into l_lob;
5 lob_replace( l_lob, '1234', '1234567890' );
6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select * from t;

X
--------------------------------------------------------------------------------
Hello 1234567890 World

ops$tkyte@DEV816>
ops$tkyte@DEV816> begin
2 for x in ( select * from t ) loop
3 lob_replace( x.x, '1234567890', '1234' );
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> select * from t;

X
--------------------------------------------------------------------------------
Hello 1234 World

ops$tkyte@DEV816>
A LOB works just like an OS file does in this regards. If you open a file for write/append -- you can seek around the file all over the place. You can write random bytes anywhere in the file. That simply overwrites the bytes that were there. If you needed to change the 1234 into 1234567890 in the middle of a file -- you have to AT LEAST rewrite the entire end of the file to open up a 6 byte slot for the extra characters. A lob is just like that -- you have to rewrite the entire end of the lob.

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Solved exactly my problem

Marc Blum, November 18, 2001 - 10:17 am UTC

Problem was: how to replace a substring inside a CLOB.

Solution: just ask tom!

Error doing something like this

Bob Maggio, June 19, 2002 - 11:02 am UTC

ORA-22920 row containing the LOB value is not locked

When I try the last part of this. I am not sure what it means as the help says Lock the row before updating the row???

Tom Kyte
June 19, 2002 - 9:46 pm UTC

In my example, I did an insert then then modified it. I had the row locked (since I inserted it and didn't commit yet)

You are probably trying this on an existing row. You need to "SELECT ... FOR UPDATE" that row to lock it.

In 9i replace function works

A reader, March 05, 2003 - 7:21 am UTC


Tom Kyte
March 05, 2003 - 8:15 am UTC

same caveat applies -- be careful what you ask for. it still has to "slide" the data, it is expensive.

What if it's a "small" CLOB?

Adrian, July 07, 2003 - 9:55 am UTC

If I know that my CLOB is > 4K, but < 32K would it be easier/quicker to write it to a variable, do my replace, then write it to a new CLOB?

Tom Kyte
July 07, 2003 - 10:50 am UTC

when you benchmarked it, what did you discover? :)

in 9i, you can actually just use REPLACE() right in SQL now.

ORA-22994

Karin, July 15, 2003 - 10:06 am UTC

I have tried the code you mentioned above about replacing a string with a string in clob fields. And it works on a few rows I have tested it on - but I get an error ORA-22994 "source offset is beyond the end of the source LOB" when I run it on the whole table. I need to replace \n with newline in 14000 rows.

Do you have any suggestions to solve this problem.



Tom Kyte
July 15, 2003 - 10:10 am UTC

find the row in question that is causing the problem and debug the code -- there must be a "bug" in the routine.

multiple repalces

umesh, December 18, 2003 - 12:04 am UTC

Tom
I tried this and have some problems Need your help SOS
I have a situation where multiple entries of the string needs to be replaced and that string can be at the end 
I did try my LEVEL BEST and at the end resorted to trial & error 

 create or replace procedure usk_clob_replace
      ( p_lob in out clob,
        p_what varchar2,
        p_with varchar2) is
     n number;
     x number;
     dupe_i number;
     type  tabtype  is table of number index by binary_integer;
     tab1 tabtype;
     begin
    for i in 1..dbms_lob.getlength(p_lob)/2
    loop
    tab1(i)  := dbms_lob.instr( p_lob, p_what,1,i );
    exit when tab1(i) =0;
    end loop;
   x:=tab1.count ;
    dbms_output.put_line(x) ;
    for i in 1..tab1.count
    loop
    dupe_i :=i;
    if ( nvl(tab1(i) ,0) > 0 ) then
    dbms_lob.copy( p_lob,
            p_lob,dbms_lob.
            getlength(p_lob) ,
            tab1(i)+length(p_with) ,
            tab1(i)+length(p_what));
    dbms_lob.write( p_lob, length(p_with), tab1(i), p_with );
     if ( length(p_what) > length(p_with) ) then
        dbms_lob.trim( p_lob,  dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
     end if;
    end if;
    end loop;
 exception
  when others then
--ORA-22994: 
--source offset is beyond the end of the source LOB
 dbms_lob.writeappend(p_lob,
             length(p_with) + length(p_what) ,
             rpad( ' ', length(p_with) + length(p_what),' '));
    dbms_lob.copy( p_lob,p_lob,
            dbms_lob.getlength(p_lob) ,
            tab1(dupe_i)+length(p_with) ,
            tab1(dupe_i)+length(p_what) );
   dbms_lob.write( p_lob,
            length(p_with),
            tab1(dupe_i),
             p_with );
   end;


create table t 
( x clob);

insert into t values ( 'abc abc abc');

  declare
       l_lob clob;
      begin
      select * into l_lob from t for update;
 usk_clob_replace ( l_lob,'213', 'aa');
  end;

SQL> select * from t;

X
---------------------------------------------------
aa 2aa21aa
 

Tom Kyte
December 18, 2003 - 11:07 am UTC

sorry but I cannot fathom what you are trying to accomplish here at all.

you'll need to specify in "text" what you are trying to do.

replace

umesh, December 19, 2003 - 5:13 am UTC

Tom 
what i meant with the above question is that i want to make this procedure work just the way replace works
SQL> select replace ( 'abcda', 'a', 1) from dual;

REPLA
-----
1bcd1


like multiple times "a" is replaced with 1
 

Tom Kyte
December 19, 2003 - 7:17 am UTC

I think this does it -- test it fully, did it rather quick.

create or replace
procedure lob_replace( p_lob in out clob,
                       p_what in varchar2,
                       p_with in varchar2 )
as
    n        number;
    l_offset number := 1;
begin
loop
    n := dbms_lob.instr( p_lob, p_what, l_offset );
    if ( nvl(n,0) > 0 )
    then
        if ( (n+length(p_what)) < dbms_lob.getlength(p_lob) )
        then
           dbms_lob.copy( p_lob,
                          p_lob,
                          dbms_lob.getlength(p_lob),
                          n+length(p_with),
                          n+length(p_what) );
        end if;
                                                                                   
        dbms_lob.write( p_lob, length(p_with), n, p_with );
        if ( length(p_what) > length(p_with) )
        then
            dbms_lob.trim( p_lob,
               dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
        end if;
        l_offset := l_offset + length(p_with);
    else
        exit;
    end if;
end loop;
end;
/
                                                                                   



ops$tkyte@ORA9IR2> insert into t values ( 'a b a c d e a' );
 
1 row created.
 
ops$tkyte@ORA9IR2> declare
  2      l_clob clob;
  3  begin
  4          select x into l_clob from t for update;
  5      lob_replace( l_clob, 'a', 1 );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
X
--------------------------------------------------------------------------------
1 b 1 c d e 1
 
ops$tkyte@ORA9IR2> update t set x = replace( x, 1, 'aaa' );
 
1 row updated.
 
ops$tkyte@ORA9IR2> select * from t;
 
X
--------------------------------------------------------------------------------
aaa b aaa c d e aaa
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t;
 
1 row deleted.
 
ops$tkyte@ORA9IR2> insert into t values ( 'e a b a c d e a e' );
 
1 row created.
 
ops$tkyte@ORA9IR2> declare
  2      l_clob clob;
  3  begin
  4          select x into l_clob from t for update;
  5      lob_replace( l_clob, 'a', 1 );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
X
--------------------------------------------------------------------------------
e 1 b 1 c d e 1 e
 
ops$tkyte@ORA9IR2> update t set x = replace( x, 1, 'aaa' );
 
1 row updated.
 
ops$tkyte@ORA9IR2> select * from t;
 
X
--------------------------------------------------------------------------------
e aaa b aaa c d e aaa e
 
 

Sliding without temporary clob problematic (8i)

Irfan Adilovic, May 17, 2005 - 5:28 pm UTC

Sorry, I cannot make a small testcase for this, it appears hard to create perfect preconditions. Anyway, the part of your (Mr. Kyte's) routine:

dbms_lob.copy( p_lob,
p_lob,
dbms_lob.getlength(p_lob),
n+p_with_length,
n+p_what_length );

fails to work properly if (?) the existing p_lob is large enough or (?) when the what's or with's length is large enough. Here, p_with_length and p_what_length are constants I introduced for the (constant) lengths of what and with.

Introducing the following:

dbms_lob.createtemporary(temp_clob,true,dbms_lob.call);
dbms_lob.copy(temp_clob,
p_lob,
dbms_lob.getlength(p_lob),
1,
n+p_what_length);
dbms_lob.copy(p_lob,
temp_clob,
dbms_lob.getlength(temp_clob),
n+p_with_length,
1);
dbms_lob.freetemporary(temp_clob);

solves the problem.

The problem appeared in the variant of your proposed routine where I replaced a substring in a clob with another clob (not string). So p_with_length could get quite big. (I used, of course, corresponding lob handling routines -- never used a varchar buffer)

Do you have an explanation for this behaviour?

Tom Kyte
May 17, 2005 - 6:36 pm UTC

I'd need a test case -- it is hard to explain that which you cannot describe.

Sliding without temporary clob problematic (8i) - 2

Irfan Adilovic, May 17, 2005 - 5:43 pm UTC

Forgot to mention the nature of the problem. The slided data appears to be slided a bit too much and in itself overlapping. As if
a
b
c
when sliding from b downwards 1 place became:
a
b <- will be overwritten
b <- start of the slided part
c <- end of the slided part
b <- but as if copied again
c <- ...

or

a
b <- will be overwritten
b <- start of the slided part
c <- end of the slided part
c <- ?

In the concrete case, I have a huge pattern which contains tags that need be replaced with some values I retreive from the database. The end result is too big and I have to use clobs for the whole result, and for the parts (partly). So, this is how one specific case *should* look like: </code> http://www.stuwo.at/~adilovici/eprog/dev/dbms_lob.copy/good.html

and this is how it looks when whe copy the clob "in-place" (without the improvement): 
http://www.stuwo.at/~adilovici/eprog/dev/dbms_lob.copy/bad.html <code>

The headings are part of the pattern, the body-text is retrieved from the database. Typically I have a tag like <spec_part:FULL_INPUT> in the pattern and I replace it with the content of a clob I retrieve from the database.

-- Irfy

Replace for CLOB in 10g ?

A reader, August 22, 2006 - 1:39 pm UTC

do we have replace function in 10g R2 for CLOB now ?

Tom Kyte
August 27, 2006 - 3:06 pm UTC

replace, lobs were made "friendlier" since 8i came out

ops$tkyte%ORA10GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA10GR2> declare
  2          l_clob clob;
  3  begin
  4          insert into t values ( 'hello world' ) returning x into l_clob;
  5          for i in 1 .. 10
  6          loop
  7                  dbms_lob.writeAppend( l_clob, 32000, rpad( '*', 32000, '*' ) );
  8          end loop;
  9          dbms_lob.writeAppend( l_clob, 5, 'end@@' );
 10          commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select dbms_lob.getlength(x), substr( x, dbms_lob.getlength(x)-10 ) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
SUBSTR(X,DBMS_LOB.GETLENGTH(X)-10)
-------------------------------------------------------------------------------
               320016
******end@@


ops$tkyte%ORA10GR2> update t set x = replace( x, 'end', 'ENDING' );

1 row updated.

ops$tkyte%ORA10GR2> select dbms_lob.getlength(x), substr( x, dbms_lob.getlength(x)-10 ) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
SUBSTR(X,DBMS_LOB.GETLENGTH(X)-10)
-------------------------------------------------------------------------------
               320019
***ENDING@@
 

dbms_lob.copy when source=destination

Jeffrey Kemp, October 03, 2006 - 4:37 am UTC

I had the same problems as Irfan Adilovic using this procedure. I'm using Oracle 9.2.0.7 currently.

create or replace
procedure lob_replace( p_lob in out clob,
p_what in varchar2,
p_with in varchar2 )
as
n number;
l_offset number := 1;
begin
loop
n := dbms_lob.instr( p_lob, p_what, l_offset );
if ( nvl(n,0) > 0 )
then
if ( (n+length(p_what)) < dbms_lob.getlength(p_lob) )
then
dbms_lob.copy( p_lob,
p_lob,
dbms_lob.getlength(p_lob),
n+length(p_with),
n+length(p_what) );
end if;


dbms_lob.write( p_lob, length(p_with), n, p_with );
if ( length(p_what) > length(p_with) )
then
dbms_lob.trim( p_lob,
dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
end if;
l_offset := l_offset + length(p_with);
else
exit;
end if;
end loop;
end;

The code sample provided (admittedly untested) fails with long LOBs in some cases, because (I believe) it is trying to copy from one section of a LOB to another section in the same LOB; sometimes, however, the section being copied FROM has just been copied TO in the same operation, and so it appears multiple substrings appear to be appended to the result, e.g. (assuming that dbms_copy starts from the leftmost character, and copies it 1 character at a time):

lob_replace('abacada','a','xxx')

p_lob = 'abacada'
n = 1
(length to copy from source = 6)
(src_offset=2 dest_offset=4 copy 'b') p_lob = 'ababada'
(src_offset=3 dest_offset=5 copy 'a') p_lob = 'ababada'
(src_offset=4 dest_offset=6 copy 'b') p_lob = 'abababa'
(src_offset=5 dest_offset=7 copy 'a') p_lob = 'abababa'
(src_offset=6 dest_offset=8 copy 'b') p_lob = 'abababab'
(src_offset=7 dest_offset=9 copy 'a') p_lob = 'ababababa'

This is assuming a lot about how dbms_lob.copy actually works underneath the hood; there's probably some buffering and other smart logic to minimise the problems with copying from a source and destination that overlap; which is probably why the routine works in most cases (i.e. for short strings).

The solution is to create a copy of the LOB, and use the copy as the source for dbms_lob.copy - the problem goes away.

Correction...?

Art Metzer, November 13, 2006 - 3:14 pm UTC

Tom,

Regarding your response to Umesh on 19-DEC-2003, shouldn't the line that reads

l_offset := l_offset + length(p_with);

...instead read:

l_offset := n + length(p_with);

?

Tom Kyte
November 14, 2006 - 4:12 am UTC

quick glance - I don't think so, did you test it and prove otherwise?

reader

A reader, November 15, 2006 - 2:52 pm UTC

I am trying to find , how to do a search using dbms_lob for lower and upper case string on a column that is of type clob. Oracle version 9.2

SQL> select col1 from t1 where dbms_lob.instr(col1,'TEST STRING',1,1) > 0;

no rows selected

SQL> select col1 from t1 where dbms_lob.instr(upper(col1),'TEST STRING',1,1) > 0;
select col1 from t1 where dbms_lob.instr(upper(col1),'TEST STRING',1,1) > 0
                                                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes
 

Tom Kyte
November 16, 2006 - 8:17 am UTC

not a good idea, read about Oracle text, create a text index on that, use contains() to search.


</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX <code>

Update

Stephane Pancrazi, November 30, 2006 - 5:51 am UTC

I modified the procedure, to support replacing character or string by blank (''). Multiple occurrences are supported.

CREATE OR REPLACE procedure lob_replace( p_lob in out clob,
p_what in varchar2,
p_with in varchar2 )
as
n number;
l_offset number := 1;
BEGIN
loop
n := dbms_lob.instr( p_lob, p_what, l_offset );
if ( nvl(n,0) > 0 )
then
if p_with is not null
then
if ( (n+length(p_what)) < dbms_lob.getlength(p_lob) )
then
dbms_lob.copy(
p_lob,
p_lob,
dbms_lob.getlength(p_lob),
n+length(p_with),
n+length(p_what) );
end if;

dbms_lob.write( p_lob, length(p_with), n, p_with );

if ( length(p_what) > length(p_with) )
then
dbms_lob.trim( p_lob,dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
end if;
l_offset := l_offset + length(p_with);
else
dbms_lob.copy(p_lob,p_lob,dbms_lob.getlength(p_lob)-length(p_what),n,n+length(p_what) );
p_lob := dbms_lob.substr(p_lob,dbms_lob.getlength(p_lob)-length(p_what), 1);

end if;

else
exit;
end if;
end loop;
end;
/

It's working perfectly, thanks

David McCarthy, December 06, 2006 - 8:29 am UTC

Just tried and tested this. Very fine.

CLOBs in Replace

Greg, November 19, 2009 - 8:28 am UTC

Ok, so far this entire thread has discussed the basic format of:
REPLACE ( <clob>, <varchar>, <varchar> );

I've tested that directly on CLOBs in 10g, and yep, works fine. However, the following still chokes:

REPLACE ( <clob>, <varchar>, <clob> );

That is, the replacement string that I'm stuffing in is quite large .. ie >32k .. REPLACE doesn't like it, throws ora-22828 error.

Here's a small script I ran to try it:

set serverout on

declare
lc_junk CLOB;
lc_rep CLOB;
lc_ff CLOB;
begin
for i in 1..20
loop
lc_junk := lc_junk || dbms_random.string('a',4000);
lc_rep := lc_rep || dbms_random.string('a',2000);
end loop;

dbms_output.put_line ( length(lc_junk) );
dbms_output.put_line ( length(lc_rep) );

lc_ff := REPLACE ( lc_junk, 'az', lc_rep );

dbms_output.put_line ( length(lc_ff) );
end;
/

SQL > @clob_test
80000
40000
declare
*
ERROR at line 1:
ORA-22828: input pattern or replacement parameters exceed 32K size limit
ORA-06512: at line 15


If I change this line:
lc_rep := lc_rep || dbms_random.string('a',2000);
to:
lc_rep := lc_rep || dbms_random.string('a',20);

It works fine (since it's just replacing with <32k ..)

SQL > @clob_test
80000
400
91144

PL/SQL procedure successfully completed.

We have a working prototype that chunks up that 2nd parameter, however, I was wondering if this is the best/only option?

For example:
if lc_junk = 'xxxxxxxxxxazxxxxxxxxxxx'
if lc_rep = 'blah'

assuming lc_rep is >32k ...
we chunk it up into smaller pieces, let's assume 2 for now.
and do:
replace(lc_junk,'az','az1az2');
replace(lc_junk,'az1','bl');
replace(lc_junk,'az2','ah');

Is this the best way of dealing with this situation? Or am I missing something that would make this easier?

Thanks!
Tom Kyte
November 23, 2009 - 3:36 pm UTC

that is not going to work. The error message is fairly explicit on this one, replace is 32k. regexp_replace is the same.

You would have to write your own search and replace - which is not too difficult actually...

You would parse the string into N strings around the 'az' string.

and then glue them together again with the new text in between.

10g clob replace...

Bill, March 15, 2011 - 7:33 am UTC

This is a weird ofshoot. Clob replace [replace (clob,'','') format] embedded in a package body. If I deploy the PB using SQL developer it all works fine, if I deploy with SQL Plus it sometimes acts as if the clob has no value (except when I log it to a logging table). This drove me crazy for months and I replaced the CLOB with a VARCHAR2(32000) and processed the string, converted back to a CLOB, parsed into XML and it worked fine no matter what the deployment method. I'm looking for some thoughts and rationale to ease my fevered brain on this.
Tom Kyte
March 15, 2011 - 8:54 am UTC

one would need an example

10g clob replace...continued

Bill, March 15, 2011 - 9:09 am UTC

http://forums.oracle.com/forums/thread.jspa?messageID=9440452#9440452

Below is the fixed code. vReplyMessage is the clob in question and vTextReply is the replacement VC2(32000). I have removed debugging code.

begin
call_SWNPost(' http://www.sendwordnow.com/usps/getNotificationResults',vMessageText, vReplyMessage, v_status_code, v_status_phrase, '');
exception
when others then
raise eJavaException;
end;
vTextReply := dbms_lob.substr( vReplyMessage, 32000, 1 );
--
IF v_status_code = 200 then
v_has_error := 'N';
ELSE
v_has_error := 'Y';
END IF;
-- we handle all exceptions below in case something goes wrong here.
-- this area can die silently.
vTextReply := replace(vTextReply,'<getNotificationResultsResponse xmlns=" http://www.sendwordnow.com/usps" >', '<getNotificationResultsResponse xmlns:xyz=" http://www.sendwordnow.com/usps" >');
--
begin
insert into swn_recip_response_t(notification_id) values (v_notification_id);
exception
when others then
null;
end;
commit;
--
begin
update swn_recip_response_t
set SWN_RECIP_RESPONSE = vTextReply
where notification_id = v_notification_id;
exception
when others then
null;
end;
commit;
--
-- parse through the XML document and update the notification and recipient records
-- parse the clob into an xml dom object
begin
vReplyMessage := vTextReply;
...
Tom Kyte
March 15, 2011 - 9:26 am UTC

No, I mean I need a real example, take your big package and whittle it down, make it standalone.

Without seeing the broken code, I cannot comment at all. I don't see a clob in any of the code, I cannot run the code, I cannot reproduce the issue.

I hate your code by the way:

exception
when others then
null;
end;


Even if you say "it is just a demo", I hate your code for having that. If it is just a demo - why have that at all???

I just looked at the 'production code' and it is worse than I thought!

when others then
if (vDebug) then
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
insert into PEMS_PROD_2.SWN_POST_LOG (log_pk, create_date, REQUEST, notification_id) values(pems_prod_2.swn_post_log_seq.nextval,sysdate,
'err_num - '||to_char(err_num)|| ' error_msg - '|| err_msg, v_notification_id);
commit;
else 
null;
end if;
end;


that hurts in so so so many ways it is not even funny.


First - you eat the exception, the caller has NO CLUE there was a failure.

Second - if you do record the error (recording is OK, NOT TELLING THE CLIENT you failed is not), you COMMIT ANY OUTSTANDING WORK!!!! How do you know it is OK to commit (rhetorical question - you do not)



At worst your exception blocks (ALL OF THEM) should be:

begin
   log_error_every_time_debug_be_darned_using_autonomous_transaction();
   RALSE;
end;




If this is true:
-- this area can die silently.

then why bother having the section AT ALL????? If it can sometimes run and sometimes NOT RUN, then by definition it can ALWAYS NOT RUN. Therefore, it is silly to run it - since the invoker will never know the difference.


clob replace

Bill, March 15, 2011 - 5:45 pm UTC

Tom I pulled the logging out for clarity as it logs based on a parameter in a table. The format is if (param) then insert into log table else null end if; There are no errors in processing It should work every time. When I log the clobs I get values in the log tables. The other statements act like the clob is null but only when it is loaded/compiled through SQL plus. My real question is not if the code works, I know it does, my question is why it does NOT work when loaded/compiled through SQL plus.
Tom Kyte
March 16, 2011 - 8:07 am UTC

There are no errors in processing It should work every time.

famous last words.

If it should work every time, why have the exception blocks at all.


There is no logical reason it should not, would not work via sqlplus as sqlplus is just an OCI client, nothing magic, nothing special, nothing you could not have written yourself.

I need a test case that can be executed by me if you want to pursue this any further. Tiny snippets do not allow us to figure out what the root cause is.

It's worse than you think

Bill, March 15, 2011 - 6:03 pm UTC

You are just seeing a few lines I need to wade through miles of this 'stuff'. This code I inherited. Since it's a standalone job it's actually proper it die silently and it is actually ok to run the commits with the code that way. I've battling the code for months and finally got access to the acceptance testing and production machines. You see we don't do our own deployments to those systems and while I have been slaving over Sql Developer I toss the code over the wall and they deploy using SQL Plus.

Yes all this code needs to be ripped out and rewritten but I had to get it working first. The really fun part is that this is a webservice that should not be called from the DB any way. Time to drink now though and battle it again later.
Tom Kyte
March 16, 2011 - 8:18 am UTC

Since it's a standalone job it's actually proper it die
silently and it is actually ok to run the commits with the code that way.


sorry, but that is nonsense. If that is true then you can simply fix the problem by erasing the code and forgetting it ever existed. The reasoning: by what you just said - it is ok if:

o the code runs not at all - without any warning.
o the code runs halfway - without any warning
o the code runs completely.

Well, if the first option is OK sometimes, then it is always OK - problem is best solved by not running the code.

but I had to get it
working first.


Here is a suggestion - a very very very serious suggestion and one that I would definitely be doing:

put a RAISE after each and every single WHEN OTHERS to make sure the exception cannot be ignore. This will UNDOUBTEDLY break the code - but it will point you to where the code is failing so you can FIX IT. There is a bug in the code somewhere, you will never find it because the coder HID it from you with a "when others" not followed by RAISE or raise application error.




If you don't do that, consider using dbms_trace so you can at least see what exceptions where thrown and where they came from:

You need to have this setup:

connect / as sysdba
@?/rdbms/admin/tracetab



and then you can:

ops$tkyte%ORA11GR2> create table t
  2  ( x int constraint check_x check (x>0),
  3    y int constraint check_y check (y>0)
  4  );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p( p_x in int, p_y in int )
  2  as
  3  begin
  4      insert into t values ( p_x, p_y );
  5      begin
  6          update t set y = -p_y where x = p_x;
  7      exception
  8          when others then null;
  9      end;
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA11GR2> exec dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p(1,1)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p(-1,-1)
BEGIN p(-1,-1); END;

*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_Y) violated
ORA-06512: at "OPS$TKYTE.P", line 4
ORA-06512: at line 1

<b>Notice how the first execution appears to have succeeded - but we know it did not, the exception was swallowed</b>

ops$tkyte%ORA11GR2> select callstack
  2    from sys.plsql_trace_events
  3   where event_kind = 52
  4   order by runid, event_seq
  5  /

CALLSTACK
-------------------------------------------------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x3a76fe2c         6  procedure OPS$TKYTE.P
0x376a09cc         1  anonymous block

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x3a76fe2c         4  procedure OPS$TKYTE.P
0x374d0e80         1  anonymous block






but now we can SEE it was swallowed and we can go back and investigate "why".

Brilliant!

Stew Ashton, March 27, 2011 - 8:11 am UTC


Tracing all PL/SQL exceptions, even the ones that are swallowed: great stuff! I am going to put this in my auditing toolkit and I just can't wait to pull it out.

Replace a encrypted string

siva, May 26, 2011 - 1:02 pm UTC

Hello,
In a column there is encrypted large string, i need to read it and display it also i need to replace a string from the decrypted data using a anonymous pl/sql block not using a function.kindly please help me in this issue.



Thanks in advance
Tom Kyte
May 26, 2011 - 1:35 pm UTC

psuedo code:

begin
for x in (select rowid rid, column from table)
loop
use whatever method you use to decrypt string here
dbms_output.putline the data
replace it
update table set column = new_value where rowid = x.rid
end loop;
end;


if you have a small amount of data, that'll work fine, if you have a ton of data - you'll need to bulk up that code using an explicit cursor, a fetch bulk collect and a forall i update. There are *tons* of examples of doing that on this site.


Not really sure why you needed to be told this - your requirement is pretty straight forward??? Nothing really fancy here.

F.KARAKAS, November 25, 2011 - 9:38 am UTC

Hi guys,

This discussion is very useful. Thanks Tom.
I have created the function version of the lob_replace replacing by varchar2 or clob.

Hope this can help someone :


CREATE OR REPLACE function f_lob_replace
(
p_lob in clob,
p_what in varchar2,
p_with in varchar2
) return clob
as
n number;
l_result clob := p_lob;
begin

n := dbms_lob.instr( p_lob, p_what);

if (nvl(n,0) > 0) then
dbms_lob.createtemporary(l_result, false, dbms_lob.call);
dbms_lob.copy(l_result,
p_lob,
n - 1,
1,
1 );
dbms_lob.writeappend(l_result, length(p_with), p_with);
dbms_lob.copy(l_result,
p_lob,
dbms_lob.getlength(p_lob) - (n + length(p_what)) + 1 ,
n + length(p_with),
n + length(p_what) );
end if;

if nvl(dbms_lob.instr(l_result, p_what), 0) > 0 then
return f_lob_replace(l_result, p_what, p_with);
end if;

return l_result;

end;
/



CREATE OR REPLACE function f_lob_replace_clob(
p_lob in clob,
p_what in varchar2,
p_with in clob ) return clob
as
n number;
l_result clob := p_lob;
begin

n := dbms_lob.instr( p_lob, p_what);

if ( nvl(n,0) > 0 )
then
dbms_lob.createtemporary(l_result, false, dbms_lob.call);
dbms_lob.copy(l_result, p_lob, n - 1, 1, 1);
dbms_lob.copy(l_result,
p_with,
dbms_lob.getlength(p_with) ,
dbms_lob.getlength(l_result) + 1,
1 );
dbms_lob.copy(l_result,
p_lob,
dbms_lob.getlength(p_lob) - (n + length(p_what)) + 1 ,
dbms_lob.getlength(l_result) + 1,
n + length(p_what) );
end if;

if nvl(dbms_lob.instr(l_result, p_what), 0) > 0 then
return f_lob_replace_clob(l_result, p_what, p_with);
end if;

return l_result;

end;
/

Fatih

function replace for CLOB proofed and tested

andreml, April 16, 2012 - 1:57 pm UTC

Sorry, all version here of replace for a clob has been turned out to be "buggi" in one or another way.
Some time ago I found an idea and implemented it.
As far as I can say this version really works...

FUNCTION replace4 (in_clob IN CLOB, in_replace_string IN VARCHAR2, in_replace_with IN VARCHAR2)
/**
* Replaces all Occurrences of a String with another within a CLOB
* Original Source see: http://66.221.222.85/reference/dbms_lob.html
* @AUTHOR: AM, 20101021,
* @PARAM: in_clob IN CLOB,
* in_replace_string IN VARCHAR2,
* in_replace_with IN VARCHAR2
* @RETURN: CLOB
* @EXCEPTION:
* @SEE:
*/
RETURN CLOB
IS
lc_this_unit_name CONSTANT VARCHAR2 (30) := 'REPLACE4';
--
lv_buffer VARCHAR2 (32767);
lv_amount BINARY_INTEGER := 16000;
lv_pos PLS_INTEGER := 1;
lv_clob_len PLS_INTEGER;
lv_retval CLOB := EMPTY_CLOB;
BEGIN
-- initalize the new clob (lv_retval)
DBMS_LOB.createtemporary (lv_retval, TRUE);
lv_clob_len := DBMS_LOB.getlength (in_clob);

WHILE lv_pos <= lv_clob_len
LOOP
DBMS_LOB.read (in_clob,
lv_amount,
lv_pos,
lv_buffer);

IF lv_buffer IS NOT NULL
THEN
-- replace the text
lv_buffer := REPLACE (lv_buffer, in_replace_string, in_replace_with);
-- write it to the new clob (lv_retval)
DBMS_LOB.writeappend (lv_retval, LENGTH (lv_buffer), lv_buffer);
END IF;

lv_pos := lv_pos + lv_amount;
END LOOP;

RETURN lv_retval;
--
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END replace4;

Replace for CLOB

Ronen, April 17, 2012 - 11:55 am UTC

To andreml,

Thank you for sharing your solution. Just one thing that might be a bug... this procedure is working on chunks of the original CLOB... What if you cut the CLOB in the middle of a string which you suppose to replace? You will miss this replacement.


xp_clob.replace2 (in_clob IN CLOB, in_replace_string IN VARCHAR2, in_replace_with IN VARCHAR2)

andreml, April 17, 2012 - 5:08 pm UTC

To Ronen (and the public)
Thank you very much for pointing out this issue.
So I took another look at another in this thread and modified it.
It uses recursion - something I usually consider to be dangerous ...
However it seems to work.
Would be nice to if someone could take another look at it:

FUNCTION replace2 (in_clob IN CLOB, in_replace_string IN VARCHAR2, in_replace_with IN VARCHAR2)
/**
* Replaces all Occurrences of a String with another within a CLOB
* Quelle siehe: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1533006062995
* @AUTHOR: AMei, 20120417
* @PARAM: in_clob IN CLOB,
* in_replace_string IN VARCHAR2,
* in_replace_with IN VARCHAR2
* @RETURN: CLOB
* @EXCEPTION:
* @SEE:
*/
RETURN CLOB
IS
lc_this_unit_name CONSTANT VARCHAR2 (30) := 'REPLACE2';
--
-- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1533006062995
/*
-- Tester
DECLARE
lv_clob CLOB := 'abc 123 abc 456 abc';
lv_replace_string VARCHAR2 (200) := 'abc';
lv_replace_with VARCHAR2 (200) := 'XXX';
lv_retval CLOB;
BEGIN
lv_retval :=
xp_clob.replace2 (in_clob => lv_clob,
in_replace_string => lv_replace_string,
in_replace_with => lv_replace_with);
-- xp_p.l (lv_retval);
END;
*/
lv_pos NUMBER;
lv_retval CLOB := in_clob;
BEGIN
lv_pos := DBMS_LOB.INSTR (in_clob, in_replace_string);

-- xp_p.l (lv_pos);

IF (NVL (lv_pos, 0) > 0)
THEN
DECLARE
-- ORA-22994 source offset is beyond the end of the source LOB
-- Cause: Check the length of the LOB and then adjust the source offset.
-- Action: The source offset for a LOB COPY or LOB LOADFROMFILE is beyond the end of the source LOB.
lx_src_offset_beyond_eo_lob EXCEPTION;
PRAGMA EXCEPTION_INIT (lx_src_offset_beyond_eo_lob, -22994);
BEGIN
DBMS_LOB.COPY (lv_retval,
lv_retval,
DBMS_LOB.getlength (lv_retval),
lv_pos + LENGTH (in_replace_with),
lv_pos + LENGTH (in_replace_string));
EXCEPTION
WHEN lx_src_offset_beyond_eo_lob
THEN
-- Occurs here when in_replace_string is the very last (sub-)string at the end of the source.
-- This Exception can be ignored in this special use case.
-- And we can go ahead to our DBMS_LOB.write... as with normal processing
NULL;
WHEN OTHERS
THEN
RAISE;
END;

DBMS_LOB.write (lv_retval,
LENGTH (in_replace_with),
lv_pos,
in_replace_with);

IF (LENGTH (in_replace_string) > LENGTH (in_replace_with))
THEN
DBMS_LOB.TRIM (
lv_retval,
DBMS_LOB.getlength (lv_retval) - (LENGTH (in_replace_string) - LENGTH (in_replace_with)));
END IF;
END IF;

IF NVL (DBMS_LOB.INSTR (lv_retval, in_replace_string), 0) > 0
THEN
-- Recursive call !
RETURN replace2 (lv_retval, in_replace_string, in_replace_with);
END IF;

RETURN lv_retval;
--
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END replace2;


still buggy

Another_User, March 28, 2014 - 9:53 pm UTC

I have not reviewed the solution from the previous person from Germany, but I can say there are still issues with the updated solution Tom provided.

SQL> CREATE OR REPLACE FUNCTION lob_replace (p_lob IN CLOB, p_what IN VARCHAR2, p_with IN VARCHAR2)
   RETURN CLOB
IS
   n          NUMBER;
   ret_clob   CLOB := EMPTY_CLOB;
   l_offset   NUMBER := 1;
BEGIN
   ret_clob := p_lob;

   LOOP
      n := DBMS_LOB.INSTR ( ret_clob, p_what, l_offset);

      IF (NVL (n, 0) > 0)
      THEN
         IF ( (n + LENGTH (p_what)) < DBMS_LOB.getlength (ret_clob))
         THEN
            DBMS_LOB.COPY (ret_clob,
                           ret_clob,
                           DBMS_LOB.getlength (ret_clob),
                           n + LENGTH (p_with),
                           n + LENGTH (p_what));
         END IF;

         DBMS_LOB.write (ret_clob,
                         LENGTH (p_with),
                         n,
                         p_with);

         IF (LENGTH (p_what) > LENGTH (p_with))
         THEN
            DBMS_LOB.TRIM ( ret_clob, DBMS_LOB.getlength (ret_clob) - (LENGTH (p_what) - LENGTH (p_with)));
         END IF;

         l_offset := l_offset + LENGTH (p_with);
      ELSE
         RETURN ret_clob;
      END IF;
   END LOOP;
END;
Function created.


SQL> select to_char(lob_replace ( 'The brown cow.',  'o' , 'oo'))  from dual

TO_CHAR(LOB_REPLACE('THEBROWNCOW.','O','OO'))                                   
--------------------------------------------------------------------------------
The broooooooown coooooow.                                                      
1 row selected.

SQL> select replace( 'The brown cow.',  'o' , 'oo')  from dual

REPLACE('THEBROWNCOW.','O','OO')
--------------------------------
The broown coow.                
1 row selected.

Replace big clobs without ORA-22828

Kevin Kirkpatrick, July 08, 2014 - 7:01 am UTC

Killing two birds with one stone here, I've invented and am sharing MREPLACE().

Background: I was working on a process which generates lots of complex SQL or PL/SQL that had to be built/run dynamically. To avoid confusing myself (and others) with lots of escaped quote marks and concatenation operators, I began making heavy use of the PL/SQL q'[ my_string ]' quoting feature in the following pattern:

DECLARE
MY_SQL CLOB;
BEGIN
...
MY_SQL := REPLACE(REPLACE(....REPLACE(TO_CLOB(q'[
----------
-- Here I put a template string filled with references,
-- e.g. {TABLE_NAME} to refer to a table name.
-- Example:
INSERT INTO {TABLE_OWNER1}.{TABLE_NAME1}
SELECT * FROM {TABLE_OWNER2}.{TABLE_NAME2}
]')
-- then my replacements go here:
,'{TABLE_OWNER1}',L_OWNER1)
,'{TABLE_NAME1}',L_NAME1)
,'{TABLE_OWNER2}',L_OWNER2)
,'{TABLE_NAME2}',L_NAME2);

I had two problems: first, the long strings of REPLACE( felt "less than elegant". Second, and more importantly, when I needed to plop a large PLSQL block in middle of another block, I'd run into ORA-22828. Inspired by some of the ideas in this thread, I came up with following work-around function:

CREATE OR REPLACE FUNCTION MREPLACE(
P_CLOB IN CLOB,
F00 IN CLOB, R00 IN CLOB DEFAULT NULL,
F01 IN CLOB DEFAULT NULL, R01 IN CLOB DEFAULT NULL,
F02 IN CLOB DEFAULT NULL, R02 IN CLOB DEFAULT NULL,
F03 IN CLOB DEFAULT NULL, R03 IN CLOB DEFAULT NULL,
F04 IN CLOB DEFAULT NULL, R04 IN CLOB DEFAULT NULL,
F05 IN CLOB DEFAULT NULL, R05 IN CLOB DEFAULT NULL,
F06 IN CLOB DEFAULT NULL, R06 IN CLOB DEFAULT NULL,
F07 IN CLOB DEFAULT NULL, R07 IN CLOB DEFAULT NULL,
F08 IN CLOB DEFAULT NULL, R08 IN CLOB DEFAULT NULL,
F09 IN CLOB DEFAULT NULL, R09 IN CLOB DEFAULT NULL,
F10 IN CLOB DEFAULT NULL, R10 IN CLOB DEFAULT NULL,
F11 IN CLOB DEFAULT NULL, R11 IN CLOB DEFAULT NULL,
F12 IN CLOB DEFAULT NULL, R12 IN CLOB DEFAULT NULL,
F13 IN CLOB DEFAULT NULL, R13 IN CLOB DEFAULT NULL,
F14 IN CLOB DEFAULT NULL, R14 IN CLOB DEFAULT NULL,
F15 IN CLOB DEFAULT NULL, R15 IN CLOB DEFAULT NULL,
F16 IN CLOB DEFAULT NULL, R16 IN CLOB DEFAULT NULL,
F17 IN CLOB DEFAULT NULL, R17 IN CLOB DEFAULT NULL,
F18 IN CLOB DEFAULT NULL, R18 IN CLOB DEFAULT NULL,
F19 IN CLOB DEFAULT NULL, R19 IN CLOB DEFAULT NULL
)
RETURN CLOB
IS
FUNCTION CLOB_REPLACE (P_CLOB IN CLOB, F IN CLOB, R IN CLOB)
RETURN CLOB
IS
L_RESULT CLOB;
BEGIN
IF P_CLOB IS NULL THEN
L_RESULT := NULL;
ELSIF F IS NULL THEN
L_RESULT := P_CLOB;
ELSIF INSTR(P_CLOB,F) = 0 THEN
L_RESULT := P_CLOB;
ELSIF LENGTH(F)<30000 AND LENGTH(R) < 30000 THEN
L_RESULT := REPLACE(P_CLOB,F,R);
ELSE
L_RESULT := SUBSTR(P_CLOB,1,INSTR(P_CLOB,F)-1)||
R ||
CLOB_REPLACE(SUBSTR(P_CLOB,INSTR(P_CLOB,F)+LENGTH(F)),F,R);
END IF;
RETURN L_RESULT;
END;
BEGIN
RETURN
CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(
CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(CLOB_REPLACE(
P_CLOB
,F00,R00) ,F01,R01) ,F02,R02) ,F03,R03) ,F04,R04) ,F05,R05) ,F06,R06) ,F07,R07) ,F08,R08) ,F09,R09)
,F10,R10) ,F11,R11) ,F12,R12) ,F13,R13) ,F14,R14) ,F15,R15) ,F16,R16) ,F17,R17) ,F18,R18) ,F19,R19);
END;


My code can now be written as:
MY_SQL := MREPLACE(q'[
--- sql referencing {VAR1} and {VAR2}
]',
'{VAR1}',L_VAR1,
'{VAR2}',L_VAR2);

Tighter code, and no more ORA-22828. Note - there is a *lot* of CLOB instantiation overhead here; this is geared toward administrative processing (i.e. called couple times per run of something that runs every hour or so) and would probably need a lot of work to function as a high-performance solution.

my well tested function for replace or remove text in large clob

Roman Kapusta, September 26, 2014 - 12:37 pm UTC


CREATE OR REPLACE FUNCTION clob_replace(
p_clob CLOB,
p_pattern varchar2,
p_replace varchar2 := null)
RETURN CLOB
deterministic
IS
v_lob clob;
v_len INTEGER := DBMS_LOB.GETLENGTH(p_clob);
v_pos INTEGER;
v_offset INTEGER := 1;
v_plen PLS_INTEGER := length(p_pattern);
v_rlen PLS_INTEGER := nvl(length(p_replace), 0);
BEGIN
if nvl(v_len, 0) = 0 or p_pattern is null then
return p_clob;
end if;

DBMS_LOB.CREATETEMPORARY(v_lob, true, 2);
DBMS_LOB.COPY(v_lob, p_clob, v_len);
loop
v_pos := DBMS_LOB.INSTR(lob_loc => v_lob, pattern => p_pattern, offset => v_offset);
if v_pos > 0 and v_pos <= v_len - v_plen then
DBMS_LOB.COPY(v_lob, v_lob, v_len - (v_pos + v_plen) + 1, v_pos + v_rlen, v_pos + v_plen);
if v_rlen > 0 then
DBMS_LOB.wtie(v_lob, v_rlen, v_pos, p_replace);
end if;
v_offset := v_pos + v_rlen;
v_len := v_len - v_plen + v_rlen;
DBMS_LOB.TRIM(v_lob, v_len);
else
return v_lob;
end if;
end loop;
end;

my well tested function for replace or remove text in large clob

Roman Kapusta, September 26, 2014 - 12:54 pm UTC

I made last minute change in my previous post and you all know how this ends. Here is correct version:
CREATE OR REPLACE FUNCTION clob_replace(
  p_clob CLOB, 
  p_pattern VARCHAR2, 
  p_replace VARCHAR2 := NULL)
  RETURN CLOB
DETERMINISTIC
IS
  v_lob CLOB;
  v_len INTEGER := DBMS_LOB.GETLENGTH(p_clob);
  v_pos INTEGER;
  v_offset INTEGER := 1;
  v_plen PLS_INTEGER := length(p_pattern);
  v_rlen PLS_INTEGER := nvl(length(p_replace), 0);
BEGIN
  IF nvl(v_len, 0) = 0 OR p_pattern IS NULL THEN
    RETURN p_clob;
  END IF;

  DBMS_LOB.CREATETEMPORARY(v_lob, TRUE, 2);
  DBMS_LOB.COPY(v_lob, p_clob, v_len);
  LOOP
    v_pos := DBMS_LOB.INSTR(lob_loc => v_lob, pattern => p_pattern, offset => v_offset);    
    IF v_pos > 0 THEN
      IF v_len - (v_pos + v_plen) + 1 > 0 THEN
        DBMS_LOB.COPY(v_lob, v_lob, v_len - (v_pos + v_plen) + 1, v_pos + v_rlen, v_pos + v_plen);
      END IF;
      IF v_rlen > 0 THEN
        DBMS_LOB.WRITE(v_lob, v_rlen, v_pos, p_replace);
      END IF;
      v_offset := v_pos + v_rlen;
      v_len := v_len - v_plen + v_rlen;
      DBMS_LOB.TRIM(v_lob, v_len);
    ELSE
      RETURN v_lob;
    END IF;
  END LOOP;
END;

what am I missing in lob_replace

umesh kasturi, May 25, 2016 - 2:00 pm UTC

For me the replace of CLOB is not working.
I followed the example as is, please help.

SQL> create or replace
2 procedure lob_replace( p_lob in out clob,
3 p_what in varchar2,
4 p_with in varchar2 )
5 as
6 n number;
7 l_offset number := 1;
8 begin
9 loop
10 n := dbms_lob.instr( p_lob, p_what, l_offset );
11 if ( nvl(n,0) > 0 )
12 then
13 if ( (n+length(p_what)) < dbms_lob.getlength(p_lob) )
14 then
15 dbms_lob.copy( p_lob,
16 p_lob,
17 dbms_lob.getlength(p_lob),
18 n+length(p_with),
19 n+length(p_what) );
20 end if;
21
22 dbms_lob.write( p_lob, length(p_with), n, p_with );
23 if ( length(p_what) > length(p_with) )
24 then
25 dbms_lob.trim( p_lob,
26 dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
27 end if;
28 l_offset := l_offset + length(p_with);
29 else
30 exit;
31 end if;
32 end loop;
33 end;
34 /

Procedure created.

SQL> delete t;

1 row deleted.

SQL> insert into t values ( 'a b a c d e a' );

1 row created.

SQL> declare
2 l_clob clob;
3 begin
4 select tname x into l_clob from t for update;
5 lob_replace( l_clob, 'a', 1 );
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from t;

TNAME
----------------------
a b a c d e a
Chris Saxon
May 26, 2016 - 4:08 am UTC

What version/platform/characterset etc are you running - your code works fine for me:


SQL> create or replace
  2  procedure lob_replace( p_lob in out clob,
  3                         p_what in varchar2,
  4                         p_with in varchar2 )
  5  as
  6      n    number;
  7  begin
  8      n := dbms_lob.instr( p_lob, p_what );
  9      if ( nvl(n,0) > 0 )
 10      then
 11          dbms_lob.copy( p_lob,
 12                         p_lob,
 13                         dbms_lob.getlength(p_lob),
 14                         n+length(p_with),
 15                         n+length(p_what) );
 16
 17          dbms_lob.write( p_lob, length(p_with), n, p_with );
 18          if ( length(p_what) > length(p_with) )
 19          then
 20              dbms_lob.trim( p_lob,
 21                 dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
 22          end if;
 23      end if;
 24  end;
 25  /

Procedure created.

SQL>
SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t ( c clob );

Table created.

SQL>
SQL> insert into t values ( 'a b a c d e a' );

1 row created.

SQL>
SQL> declare
  2   l_clob clob;
  3   begin
  4    select c x into l_clob from t for update;
  5    lob_replace( l_clob, 'a', 1 );
  6    end;
  7    /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

C
--------------------------------------------------------------------------------
1 b a c d e a

1 row selected.

SQL>


dbms_lob.write using raw parameter 32k limit

Dahikino, June 29, 2022 - 10:45 am UTC

we encoutered a problem with the procedure lob_replace due to the limitation of the raw datatype upon 32k Byte in Oracle 12c.

Adding a loop to write by chunk of 32k with dmbs_lob.write solved the problem.

Best regards
Chris Saxon
June 29, 2022 - 11:07 am UTC

Thanks for sharing

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here