3 Replies Latest reply: Apr 4, 2013 8:20 AM by odie_63 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.
        • 1. Re: The problem with ampersand in PL/SQL procedure
          odie_63
          A dont need '&' in this output. I need to parse this variable to erase all ampersands.
          No, you do need to keep "&" in the output. Otherwise you'll end up with a mess of invalid XML.

          The <ns:return> element contains an XML payload in its escaped form.
          What you have to do is extract this part, unescape it, and finally parse it just as any other document.
          • 2. Re: The problem with ampersand in PL/SQL procedure
            Khayyam
            Then how to convert it to usual '<' ?

            how to replace '& l t ;' to '<' ? (without escape)
            • 3. Re: The problem with ampersand in PL/SQL procedure
              odie_63
              SQL> declare
                2  
                3    data_out clob :=
                4  '<?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"?>
                5  &lt;response>
                6       &lt;retval>-2200003&lt;/retval>
                7       &lt;retmsg>Invalid Service&lt;/retmsg>
                8       &lt;quantity>&lt;/quantity>
                9       &lt;cube-transaction-id>&lt;/cube-transaction-id>
               10  > &lt;/response></ns:return></ns:chargeSubscriberResponse></soapenv:Body></soapenv:Envelope>' ;
               11  
               12  begin
               13  
               14    for rec in (
               15        select r.*
               16        from xmltable(
               17               xmlnamespaces(
               18                 'http://www.w3.org/2003/05/soap-envelope' as "soap"
               19               , 'http://ws.cube.az' as "ns"
               20               )
               21             , '/soap:Envelope/soap:Body/ns:chargeSubscriberResponse'
               22               passing xmlparse(document data_out)
               23               columns xml_return clob path 'ns:return'
               24             ) x
               25           , xmltable('/response'
               26               passing xmlparse(document x.xml_return)
               27               columns retval              number       path 'retval'
               28                     , retmsg              varchar2(30) path 'retmsg'
               29                     , quantity            number       path 'quantity'
               30                     , cube_transaction_id varchar2(30) path 'cube-transaction-id'
               31             ) r
               32    )
               33    loop
               34  
               35      dbms_output.put_line('retval = '||rec.retval);
               36      dbms_output.put_line('retmsg = '||rec.retmsg);
               37      dbms_output.put_line('quantity = '||rec.quantity);
               38      dbms_output.put_line('cube_transaction_id = '||rec.cube_transaction_id);
               39  
               40    end loop ;
               41  
               42  end;
               43  /
               
              retval = -2200003
              retmsg = Invalid Service
              quantity = 
              cube_transaction_id = 
               
              PL/SQL procedure successfully completed