I created following function
function lob_replace( p_lob in out clob,p_what in varchar2,p_with in clob ) return clob
as
l_temp_number number;
l_temp_number_1 number;
l_temp_clob clob;
l_return_clob clob;
l_temp1_clob clob;
l_temp2_clob clob;
begin
l_temp_number:=dbms_lob.instr(p_lob, p_what);
--Create a lob locator
DBMS_LOB.createtemporary(l_temp_clob,true);
DBMS_LOB.createtemporary(l_temp1_clob,true);
DBMS_LOB.createtemporary(l_temp2_clob,true);
---substract and build the LOBs
l_temp_number_1:=length(p_lob);
l_temp_clob:=dbms_lob.substr(p_lob,l_temp_number-1,1);
l_temp1_clob:=dbms_lob.substr(p_lob,l_temp_number_1-l_temp_number+1 ,l_temp_number +length(p_what) );
--append three diff lob to one
dbms_lob.append(l_temp2_clob,l_temp_clob);
dbms_lob.append(l_temp2_clob,p_with);
dbms_lob.append(l_temp2_clob,l_temp1_clob);
l_return_clob :=l_temp2_clob;
--remove the tmp lob
DBMS_LOB.freetemporary(l_temp_clob);
DBMS_LOB.freetemporary(l_temp1_clob);
DBMS_LOB.freetemporary(l_temp2_clob);
return l_return_clob;
end;
this will throw error if I call this function as below
declare
temp clob;
begin
temp:='replace this #a#';
temp:=lob_replace(temp,'#a#','with this');
end;
it will throw below error
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 639
ORA-06512: at "LOB_REPLACE", line 24
ORA-06512: at line 5
but this will not throw error
declare
temp clob;
begin
temp:='replace this #a# ';
temp:=lob_replace(temp,'#a#','with this');
end;
please note the extra space at the end on temp:='replace this #a# ';
does any one knows the reason for this?
When you are constructing
l_temp1_clob
, you can only add the rest of the lob, if there is anything to add. Change you code:To this:
The logic is: if the start of the lob substr is before the end of the lob.
Also, the
dd
variable is not declared in your test call: