13 Replies Latest reply: Apr 4, 2013 10:42 AM by Khayyam RSS

    The problem with ampersand in PL/SQL procedure

    Khayyam
      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.