I am having a bizarre problem with a PL/SQL replace call on using CLOBS on one of my databases. The code properly works on other databases I have but on this one particular one it doesn't.
I have 2 simple routines below, 1 takes its inputs as a clob, the other as a varchar2, on all my other database servers, the replace works properly for both the method that uses clobs and the method that uses varchar2s.
However on this server the CLOB version doesn't actually replace anything, it just returns out the original string as the result of the replace even if the string to be replaced exists in the input. The VARCHAR2 version properly replaces the substring.
Belows is the Code, and below that is my output from running the stored procedures on the same inputs.
Clearly this is something at the database configuration issue as far as I can tell, but my DBA's so far can't seem to offer me much in the way of a solution, so any ideas would be appreciated. (AGAIN, THE CLOB CODE RUNS FINE ON ALL OTHER SERVERS, JUST THIS ONE SERVER GIVES ME THE PROBLEM).. all are 9.2.0.8.0 servers.
Any help would be most appreciated.
SOURCE:
procedure pr_write_test (xmlDoc in CLOB) is
action VARCHAR2(50);
xmlDoc2 CLOB;
xmlnsVal VARCHAR2(200) := 'xmlns="http://FindersWS/DailyFundPerformance"';
begin
xmlDoc2 := replace (xmlDoc, xmlnsVal, '');
dbms_output.put_line ('HELLO');
dbms_output.put_line(xmlDoc2);
end;
procedure pr_write_test2 (xmlDoc in VARCHAR2) is
action VARCHAR2(50);
xmlDoc2 VARCHAR2(3000);
xmlnsVal VARCHAR2(200) := 'xmlns="http://FindersWS/DailyFundPerformance"';
begin
xmlDoc2 := replace (xmlDoc, xmlnsVal, '');
dbms_output.put_line ('HELLO');
dbms_output.put_line(xmlDoc2);
end;
RUNTIME: (TEST2 is the VARCHAR version, TEST is the CLOB version)
SQL> begin
2 pkg_xml_dfp.pr_write_test2('<FundPerformanceResponse xmlns="http://FindersW
S/DailyFundPerformance" ><messageHeader><Source>DFP TRIGGER</Source><StatusCode>
UPDATE</StatusCode></messageHeader></FundPerformanceResponse>');
3 end;
4 /
HELLO
<FundPerformanceResponse ><messageHeader><Source>DFP
TRIGGER</Source><StatusCode>UPDATE</StatusCode></messageHeader></FundPerformance
Response>
PL/SQL procedure successfully completed.
SQL> begin
2 pkg_xml_dfp.pr_write_test('<FundPerformanceResponse xmlns="http://FindersWS
/DailyFundPerformance" ><messageHeader><Source>DFP TRIGGER</Source><StatusCode>U
PDATE</StatusCode></messageHeader></FundPerformanceResponse>');
3 end;
4 /
HELLO
<FundPerformanceResponse xmlns="http://FindersWS/DailyFundPerformance"
<messageHeader><Source>DFP
TRIGGER</Source><StatusCode>UPDATE</StatusCode></messageHeader></FundPerformance
Response>