insert XML request into clob column using Oracle

I do have XML request as below:

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:toy="ToyotaWebServiceHost" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
   <soapenv:Header/>
   <soapenv:Body>
      <toy:CreateOrder soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <orderToCreate xsi:type="enc:WebServiceOrder" xmlns:enc="ToyotaWebServiceHost/encoded">
            <OrderRows xsi:type="enc:ArrayOfWebServiceOrderRow" soapenc:arrayType="enc:WebServiceOrderRow[]"/>
            <OrderId xsi:type="xsd:string">?</OrderId>
         </orderToCreate>
      </toy:CreateOrder>
   </soapenv:Body>
</soapenv:Envelope>

I wanted to insert it into a table in clob column but due to double quotes (” “) it is not insertable and below error is observed . SQL Error: ORA-01756: quoted string not properly terminated it is not insertable. Please suggest

Answer

Oracle uses single quotes to delimit strings.

Use single quotes, there is no need to escape double quotes – see example below

create table tab (my_clob clob);

insert into tab (my_clob)
values (
'<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:toy="ToyotaWebServiceHost" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
   <soapenv:Header/>
   <soapenv:Body>
      <toy:CreateOrder soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <orderToCreate xsi:type="enc:WebServiceOrder" xmlns:enc="ToyotaWebServiceHost/encoded">
            <OrderRows xsi:type="enc:ArrayOfWebServiceOrderRow" soapenc:arrayType="enc:WebServiceOrderRow[]"/>
            <OrderId xsi:type="xsd:string">?</OrderId>
         </orderToCreate>
      </toy:CreateOrder>
   </soapenv:Body>
</soapenv:Envelope>' 
);

1 row inserted.

Advertisement

Answer