This discussion is archived
13 Replies Latest reply: Apr 4, 2013 8:42 AM by Khayyam RSS

The problem with ampersand in PL/SQL procedure

Khayyam Explorer
Currently Being Moderated
Dear Oracle Experts,

Please help me to figure out this case. I have the following code
V_RESPONSE := UTL_HTTP.GET_RESPONSE (V_REQUEST);

      LOOP
         UTL_HTTP.READ_LINE (V_RESPONSE, V_BUFFER, FALSE);
         DATA_OUT := DATA_OUT || V_BUFFER;
      --DBMS_OUTPUT.PUT_LINE (V_BUFFER);
      END LOOP;
And I am getting DATA_OUT like
<?xml version='1.0' encoding='UTF-8'?><soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope"><soapenv:Body><ns:chargeSubscriberResponse xmlns:ns="http://ws.cube.az"><ns:return>&lt;?xml version="1.0" encoding="UTF-8"?>
&lt;response>
     &lt;retval>-2200003&lt;/retval>
     &lt;retmsg>Invalid Service&lt;/retmsg>
     &lt;quantity>&lt;/quantity>
     &lt;cube-transaction-id>&lt;/cube-transaction-id>
&lt;/response></ns:return></ns:chargeSubscriberResponse></soapenv:Body></soapenv:Envelope>
A dont need '&' in this output. I need to parse this variable to erase all ampersands. How can I do it ?

Just replace doesnt work... I tried
PROCEDURE Clean_up_xml (v_xml IN OUT VARCHAR2)
   IS
   BEGIN
      WHILE INSTR (v_xml, 'lt;') > 0
      LOOP
         v_xml :=
            SUBSTR (v_xml, 1, INSTR (v_xml, 'lt;') - 2)
            || SUBSTR (v_xml, INSTR (v_xml, 'lt;') + 4);
      END LOOP;
   END;
But no luck...

The procedure doesnt erase '&'. How can I disable ampersand in PL/SQL procedure ?

Thanks in advance.

P.S.: I know about SET DEFINE OFF. But it works only in SQL*PLUS.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points