xmlcharacteroracle-sqldeveloperreserved

How to create function for reserved xml characters in sql


Please any one help for me to create function for reserved XML Character in SQL. The below I've tried the below function but it is not working for me.

function escape_char(p_text varchar2) return varchar2 is
begin
     return (replace(p_text, '<','&lt;'));
     return (replace(p_text, '>','&gt;'));
     return (replace(p_text, '&','&amp;'));
     return (replace(p_text, '''','&apos;'));
     return (replace(p_text, '"','&quot;'));
end;

Solution

  • When you use a RETURN in a function, the function will return some value and end; this way, in your function, only the first replace will be run, and the code below will never be executed.

    This is a simple way to edit it:

    create or replace function escape_char(p_text varchar2) return varchar2 is
        vRetVal     varchar2(100);
    begin
         vRetVal := replace(p_text, '&','&amp;');
         vRetVal := replace(vRetVal, '<','&lt;'); 
         vRetVal := replace(vRetVal, '>','&gt;');
         vRetVal := replace(vRetVal, '''','&apos;');
         vRetVal := replace(vRetVal, '"','&quot;');
         --
         return vRetVal;
    end;