Today we will show how to access data from WebServices directly in PL/SQL, as I promised in the previous article.

[Česká verze článku – Volání Webové služby (Webservice) v PL/SQL pomocí UTL_HTTP]

For this purpose you need:

  1. Tool for development/testing web services. Applying package SoapUI
  2. SQL*Plus environment. For our tests will fully comply.

Creating a WebService (for testing)

Create a test web service described by the following WSDL document.

<?xml version="1.0" encoding="UTF-8"?>
<definitions name="HelloService"
   targetNamespace="http://www.ecerami.com/wsdl/HelloService.wsdl"
   xmlns="http://schemas.xmlsoap.org/wsdl/"
   xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
   xmlns:tns="http://www.ecerami.com/wsdl/HelloService.wsdl"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema">

   <message name="SayHelloRequest">
      <part name="firstName" type="xsd:string"/>
   </message>
   <message name="SayHelloResponse">
      <part name="greeting" type="xsd:string"/>
   </message>

   <portType name="Hello_PortType">
      <operation name="sayHello">
         <input message="tns:SayHelloRequest"/>
         <output message="tns:SayHelloResponse"/>
      </operation>
   </portType>

   <binding name="Hello_Binding" type="tns:Hello_PortType">
      <soap:binding style="rpc"
         transport="http://schemas.xmlsoap.org/soap/http"/>
      <operation name="sayHello">
         <soap:operation soapAction="sayHello"/>
         <input>
            <soap:body
               encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
               namespace="urn:examples:helloservice"
               use="encoded"/>
         </input>
         <output>
            <soap:body
               encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
               namespace="urn:examples:helloservice"
               use="encoded"/>
         </output>
      </operation>
   </binding>

   <service name="Hello_Service">
      <documentation>WSDL File for HelloService</documentation>
      <port binding="tns:Hello_Binding" name="Hello_Port">
         <soap:address
            location="http://localhost:8088/mockHello_Binding"/>
      </port>
   </service>
</definitions>

Install and run the program SoapUI. The new project will create over SoapUI menu item File -> New Project Soap UI.

Create new SoapUI project
Create new SoapUI project

Generate Mock Service – Figure shows that the WSDL document describes a service with one method sayHello.

Create MOCK service...
Create MOCK service...

Mock enter a name for service – I leave the default, which offers SoapUI.

MOCK service name
MOCK service name

Start testing the service (if you leave the default settings, the service is mapped to port 8088).

Click to run test service...
Click to run test service...

Open and display the WSDL document in a browser window.

Click to open browser window
Click to open browser window

Here is a browser window with the resulting WSDL document test that returns Web Service “HelloService”.

WSDL in Internet Explorer
WSDL in Internet Explorer

Run the query method sayHello. You should see the following.

Run sayHello method
Run sayHello method

Edit (27.9.2012): Tip for Mockup Response.
This simple modification can facilitate Response Mockup testing:

Tip for Mockup Response
Run sayHello method

Content query to the server (SOAP Request):

<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:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHello soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <firstName xsi:type="xsd:string">Martin Mareš</firstName>
      </urn:sayHello>
   </soapenv:Body>
</soapenv:Envelope>

Content server response (SOAP Response):

<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:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHelloResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <greeting xsi:type="xsd:string">gero et</greeting>
      </urn:sayHelloResponse>
   </soapenv:Body>
</soapenv:Envelope>

Consuming WebService using the PL/SQL package UTL_HTTP

We let our service Mock test run in a window SoapUI and we get into the PL/SQL code, SQL*Plus. A prerequisite for the proper functioning of the following PL/SQL code is that you get from your development/testing of the Oracle database on a computer where you are running a test web services on port 8088.

When you run the code consuming a Web service may experience the following error:

ORA-29273: HTTP request failed
ORA-06512: on "SYS.UTL_HTTP", line 1130
ORA-24247: access control list (ACL) did not allow access to the network
ORA-06512: on line 22

If that happens, you need to set permissions on the connect call and resolve (name resolve over DNS) the Oracle database. We provide the following script (in my case, triggered by the user SCOTT). In the script, please note principal line (which should include the name of the user for which permissions you assign), the item privilege (privilege name “connect” and “resolve“) and a guest entry (you can fill in either the Hostname or IP address of the computer is running Web service)

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'Test ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'MY-NOTEBOOK-HOSTNAME');
END;
/
COMMIT
/

If you set permissions correctly above, it should become operational next script demonstrating the Request/Response test our WebService. (The script, pay attention to variables l_host_name and l_port, values should match those you set)

DECLARE
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_buffer_size    NUMBER(10) := 512;
    l_line_size      NUMBER(10) := 50;
    l_lines_count    NUMBER(10) := 20;
    l_string_request VARCHAR2(512);
    l_line           VARCHAR2(128);
    l_substring_msg  VARCHAR2(512);
    l_raw_data       RAW(512);
    l_clob_response  CLOB;
    l_host_name      VARCHAR2(128) := 'MY-NOTEBOOK-HOSTNAME';
    l_port           VARCHAR2(128) := '8088';

BEGIN
    l_string_request := '<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:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHello soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <firstName xsi:type="xsd:string">Martin</firstName>
      </urn:sayHello>
   </soapenv:Body>
</soapenv:Envelope>';
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'http://' || l_host_name || ':' || l_port || '/mockHello_Binding', method => 'POST', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Host', l_host_name || ':' || l_port);
    UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset=UTF-8');
    UTL_HTTP.set_header(l_http_request, 'SOAPAction', '"sayHello"');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));

    <<request_loop>>
    FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
        l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);

        BEGIN
            l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
            UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT request_loop;
        END;
    END LOOP request_loop;

    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));

    <<print_response>>
    FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
        l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
        DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || l_line);
        EXIT WHEN i > l_lines_count - 1;
    END LOOP print_response;

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of the script is as follows:

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "483"

=== Print first 20 lines of HTTP response... ===

[00]: <soapenv:Envelope xmlns:xsi="http://www.w3.org/200
[01]: 1/XMLSchema-instance" xmlns:xsd="http://www.w3.org
[02]: /2001/XMLSchema" xmlns:soapenv="http://schemas.xml
[03]: soap.org/soap/envelope/" xmlns:urn="urn:examples:h
[04]: elloservice">
   <soapenv:Header/>
   <soapenv:B
[05]: ody>
      <urn:sayHelloResponse soapenv:encoding
[06]: Style="http://schemas.xmlsoap.org/soap/encoding/">
[07]:
         <greeting xsi:type="xsd:string">gero et
[08]: </greeting>
      </urn:sayHelloResponse>
   </s
[09]: oapenv:Body>
</soapenv:Envelope>

Consuming a real WebService CDYNE Weather

In conclusion, we show the real consumption of WebService CDYNE Weather (from WSDL description), specifically a method call GetCityForecastByZIP. Calling the following script assumes settings (ACL) in an Oracle database (just as I mentioned above in the article):

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'ws.cdyne.com');
END;
/
COMMIT
/

And now the promise of a script:

DECLARE
    l_http_request    UTL_HTTP.req;
    l_http_response   UTL_HTTP.resp;
    l_buffer_size     NUMBER(10) := 512;
    l_line_size       NUMBER(10) := 50;
    l_lines_count     NUMBER(10) := 20;
    l_string_request  VARCHAR2(512);
    l_line            VARCHAR2(128);
    l_substring_msg   VARCHAR2(512);
    l_raw_data        RAW(512);
    l_clob_response   CLOB;
    l_host_name       VARCHAR2(128) := 'ws.cdyne.com';
    l_port            VARCHAR2(128) := '80';
    l_zip             VARCHAR2(128) := '94065'; -- ZIP for Oracle corporation (Redwood City)
    l_resp_xml        XMLType;
    l_result_XML_node VARCHAR2(128);
    l_NAMESPACE_SOAP  VARCHAR2(128) := 'xmlns="http://www.w3.org/2003/05/soap-envelope"';
    l_response_city   VARCHAR2(128);
    l_response_date   VARCHAR2(128);
    l_response_temp   VARCHAR2(128);

BEGIN
    l_string_request := '<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <GetCityForecastByZIP xmlns="http://ws.cdyne.com/WeatherWS/">
      <ZIP>' || l_zip || '</ZIP>
    </GetCityForecastByZIP>
  </soap12:Body>
</soap12:Envelope>';
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'http://' || l_host_name || ':' || l_port || '/WeatherWS/Weather.asmx', method => 'POST', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/soap+xml; charset=utf-8');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));

    <<request_loop>>
    FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
        l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);

        BEGIN
            l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
            UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT request_loop;
        END;
    END LOOP request_loop;

    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print result ===' || CHR(10) || CHR(10));

    IF(l_http_response.status_code = 200) THEN
        -- Create XML type from response text
        l_resp_xml := XMLType.createXML(l_clob_response);
        -- Clean SOAP header
        SELECT EXTRACT(l_resp_xml, 'Envelope/Body/node()', l_NAMESPACE_SOAP) INTO l_resp_xml FROM dual;
        -- Extract City
        l_result_XML_node := 'GetCityForecastByZIPResponse/GetCityForecastByZIPResult/';
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'City[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_city FROM dual;
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'ForecastResult[1]/Forecast[1]/Date[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_date FROM dual;
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'ForecastResult[1]/Forecast[1]/Temperatures[1]/DaytimeHigh[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_temp FROM dual;
    END IF;

    DBMS_OUTPUT.put_line ( 'Result> l_response_city=' || l_response_city);
    DBMS_OUTPUT.put_line ( 'Result> l_response_date=' || l_response_date);
    DBMS_OUTPUT.put_line ( 'Result> l_response_temp=' || l_response_temp);

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of the script is as follows:

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "2673"

=== Result... ===

Result> l_response_city=Redwood City
Result> l_response_date=2010-08-17T00:00:00
Result> l_response_temp=73

Post to Twitter

35 Comments

  1. gustavo

    Reply

    to run on 1 script, shoot me now ora-24813 cannot send or recieve an unsupported LOB

  2. Swadhin

    Reply

    I was just wondering if I have a website(Lets take an example of any help desk URL ) and I want to login and update or comment on any ticket then can we use UTL_HTTP and SOAP concept…

    Like login to URL and update ticket status for a ticket number.

    Wish if we can do this in PLSQL.

    • Martin Mareš

      Reply

      Yes, it can be done using the package UTL_HTTP. If you use HTTPS connections, it is more difficult. You must first create a wrapper for a Java function that will perform a GET/SET operations. In Java, you must use a security provider, such as com.sun.net.ssl.internal.ssl.Provider.

      The server must have saved the certificate file, for example in the directory:

      /home/oracle/Wallets/truststore.file

      … and then set the following Java code:

      System.setProperty (“java.protocol.handler.pkgs”, “com.sun.net.ssl.internal.www.protocol”);
      System.setProperty (“javax.net.ssl.trustStore”, “/home/oracle/Wallets/truststore.file”);
      System.setProperty (“javax.net.ssl.trustStorePassword”, “oraclewalletpassword”);

      Perhaps in future I will write another article on SOAP over HTTPS…

      • James Taylor

        Reply

        I am doing HTTPS with UTL_HTTP and a Wallet. But having trouble with one of the methods that has an xml string input parameter. Is this possibly why you ended up going the JAVA route?

  3. JB

    Reply

    Martin…I am a pl sql developer in USA/Virgina, trying to do a http post/get and retrieve xml results in the get.

    The url is a .asp url/web service.

    The post parameters are in xml form and I can’t figure out where they go in the code. I tested your weather procedure…worked nicely.

    Example parameters are (there are many…but here showing just a few for format below)

    Any feedback on how these xml params would be applied/passed into the post is appreciated.

  4. JB

    Reply

    Example parameters:xml brakets removed so it would post to blob

    tag RECEIVING_PARTY _Name=”8888″ end tag
    tag SUBMITTING_PARTY _Name=”CREDITWELL” end tag
    tag REQUEST RequestDatetime=”2006-09-18T07:29:33″ InternalAccountIdentifier=”123″ LoginAccountIdentifier=”TEST” LoginAccountPassword=”interface1 end tag

  5. Ripon

    Reply

    Hi Martin

    I want to call a wsdl that returns files (excel/ msword etc). Please let me know how should I call. Since those would be big files, and wsdl should be called several times to get those file, I would really appreciate, if you please provide me some sample.

    Regards

    Zi

    • Martin Mareš

      Reply

      Hi Zi,

      If you want to to return through the WS large files, it is best to use a combination of compression + BASE64 encoding. By compressing the output file first and then encode BASE64 encoding, you can transfer relatively little data. The sample message might look like this:

      — SOAP Request

      <?xml version="1.0" encoding="UTF-8"?>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
      <soapenv:Header/>
      <soapenv:Body>
      <GetBigFileReq xmlns="http://bigFileNamespace/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <fileId>123</fileId>
      </GetBigFileReq>
      </soapenv:Body>
      </soapenv:Envelope>

      — SOAP Response


      <?xml version="1.0" encoding="UTF-8"?>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
      <soapenv:Header/>
      <soapenv:Body>
      <GetBigFileResp xmlns="http://bigFileNamespace/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <fileId>123</fileId>
      <fileName>TestFile.txt</fileName>
      <fileDataBase64>![CDATA[H4sICPSwhk8AC1Rlc3RGaWxlLnR4dABNkbGOGzEMRHsD/gd21xwMX3EBUqY8wAHy
      C1wt7RUsUYJIebN/n9E6QdyJIDnzOLqUJpm+qvVM0chirmmjuee8kctvp3IlX4Rq
      i+pRb8Q6k29VTHyvo87dvG2n4+HyorWw0SSi+/K/mTcYOAS4za8W8pAGZw2yT398
      ns/2fjysC9ZZqetdy6pPBEx6KXdiunFKsu18wNm5LDTOU5KZomOMMt/RePatSogZ
      ihPWAfvlO6P19ogPbGjBrYrbrygpiHpvUeydpu7EycrOloQrzoG2JAneisbwGgew
      kQFHHdGIGWQiODccERbWm8wngvMK51pqT9yiDdxnTh/fv52N1ujL8TDqBhM2GTde
      xBueTraIuFEo6n9tXmOvbMY3GfGNQDJaUBnXgGEI0yx291Kp9ilFW4aAlauv3OR4
      SBGB/Uj4LfoFmZ/IrwEupD6PQfyTxaK2A/13Pf0B0Y2NLEUCAAA=]]</fileDataBase64>
      </GetBigFileResp>
      </soapenv:Body>
      </soapenv:Envelope>

      — How to get data from the tag ?

      1. save the content of the tag to file “TestFile.txt.b64”
      2. from command line run: “base64 -d TestFile.txt.b64 > TestFile.txt.gz”
      3. from command line run: “gunzip TestFile.txt.gz”
      4. It originated TestFile.txt file you should see this content:


      Lorem Ipsum is simply dummy text of the printing and typesetting industry.
      Lorem Ipsum has been the industry’s standard dummy text ever since the 1500s,
      when an unknown printer took a galley of type and scrambled it to make a type specimen book.
      It has survived not only five centuries, but also the leap into electronic typesetting,
      remaining essentially unchanged. It was popularised in the 1960s with
      the release of Letraset sheets containing Lorem Ipsum passages,
      and more recently with desktop publishing software
      like Aldus PageMaker including versions of Lorem Ipsum.

      • Zi

        Reply

        Hi Martin

        Thanks for your reply. I really appreciate this.

        My situation is little different. I am calling UCM (Oracle Content Management) to get a file. This file could be pdf/doc/excel and file size could differ. I am using a soap call which returns something like below (pls see reponse). I need to get the file from below soap response. I am calling wsdl from APEX (Pl/sql) and need to download this file from a web link. I need to use pl/sql (since it is apex client) to call the wsdl. I would appreciate you any help.

        Example
        =======

        FY2020File Download Link

        Request
        =======

        <!–Optional:–>
        01_MISC
        <!–Optional:–>
        LatestReleased

        Response
        ========

        FY12-0149_MISC_14012
        GetFile.wsdl
        Document
        GM
        Public

        9003
        8803
        1
        RELEASED

        xxx.doc
        PD94bWwgdmVyc2lv…base64data…+DQo=

        0
        You are logged in as ‘test

        I would really appreciate your any help.

        Zi

          • Zi

            Hi Martin

            Really appreciate your time and good intention to help me our.

            I just need help a sample example to download a file(msword/excle etc) from a web link using Pl/Sql. Even though I am using APEX, but that has very minimum effect on this, since I am using pure Pl/Sql.

            Another question, in the response I am getting fileContent tag with value on it. Is that is the thing I need extract? how do I convert it to actual file using pl/sql?

            I would really appreciate your help.

            Thanks/Zi

            test_test.pptx
            idc:fileContent>UEsDBBQABgAIAAAAIQDfzBj1wgEAAEYMAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAAAAAAAAAAAAAAAAAA

  6. Sreelesh

    Reply

    Hi Martin,
    Can you tell me how to upload an image to a third party server and get response (which is xml) using pl sql. It must be a HTTP POST with content type as multipart/form-data.

    Thanks

  7. Edin

    Reply

    Dear Martin,

    When I send SOAP envelope with ”Martin Mareš” got error:

    ORA-29269: HTTP server error 500 – Internal Server Error

    ORA-06512: at line 9

    But when I send ENVELOPE with ”Martin Mares” everything is fine???

    I see here that encoding is UTF-8, but still have problems.

    Do you have this problems???

    • Martin Mareš

      Reply

      Hello Edin, It was a challenge for me :-)

      The key is to add the following lines in the PL/SQL script:

          UTL_HTTP.set_header( l_http_request, 'Transfer-Encoding', 'chunked');
          UTL_HTTP.set_body_charset( l_http_request, l_xml_encoding);
      

      Here is a complete modification of the sample script:

      DECLARE
          l_http_request   UTL_HTTP.req;
          l_http_response  UTL_HTTP.resp;
          l_buffer_size    NUMBER(10) := 1024;
          l_line_size      NUMBER(10) := 50;
          l_lines_count    NUMBER(10) := 20;
          l_string_request VARCHAR2(1024);
          l_line           VARCHAR2(128);
          l_substring_msg  VARCHAR2(1024);
          l_raw_data       RAW(1024);
          l_clob_response  CLOB;
          l_host_name      VARCHAR2(128) := 'MY-NOTEBOOK-HOSTNAME';
          l_port           VARCHAR2(128) := '8088';
          l_xml_encoding   VARCHAR2(128) := 'UTF-8';
      
      BEGIN
          l_string_request := '<?xml version="1.0" encoding="' || l_xml_encoding || '"?>
         <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:urn="urn:examples:helloservice">
         <soapenv:Header/>
         <soapenv:Body>
            <urn:sayHello soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
               <firstName xsi:type="xsd:string">ěščřžýáíé</firstName>
            </urn:sayHello>
         </soapenv:Body>
      </soapenv:Envelope>';
          UTL_HTTP.set_transfer_timeout(60);
          l_http_request := UTL_HTTP.begin_request(url => 'http://' || l_host_name || ':' || l_port || '/mockHello_Binding', method => 'POST', http_version => 'HTTP/1.1');
          UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
          UTL_HTTP.set_header(l_http_request, 'Host', l_host_name || ':' || l_port);
          UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
          UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset='||l_xml_encoding);
          UTL_HTTP.set_header(l_http_request, 'SOAPAction', '"sayHello"');
          UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));
      
          UTL_HTTP.set_header( l_http_request, 'Transfer-Encoding', 'chunked');
          UTL_HTTP.set_body_charset( l_http_request, l_xml_encoding);
      
          <<request_loop>>
          FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
              l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);
      
              BEGIN
                  l_raw_data := UTL_RAW.cast_to_raw(l_substring_msg);
                  UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
                  EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                          EXIT request_loop;
              END;
          END LOOP request_loop;
      
          l_http_response := UTL_HTTP.get_response(l_http_request);
          DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
          DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
          DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');
      
          BEGIN
      
              <<response_loop>>
              LOOP
                  UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
                  l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
              END LOOP response_loop;
      
              EXCEPTION
                  WHEN UTL_HTTP.end_of_body THEN
                      UTL_HTTP.end_response(l_http_response);
          END;
          DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
          DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));
      
          <<print_response>>
          FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
              l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
              DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || l_line);
              EXIT WHEN i > l_lines_count - 1;
          END LOOP print_response;
      
          IF l_http_request.private_hndl IS NOT NULL THEN
              UTL_HTTP.end_request(l_http_request);
          END IF;
      
          IF l_http_response.private_hndl IS NOT NULL THEN
              UTL_HTTP.end_response(l_http_response);
          END IF;
      
      END;
      /
      

      But do not forget that it also depends on the NLS_LANG* parameter setting of DB. For me it is as follows:

      SELECT PARAMETER || ' = ' || VALUE  FROM NLS_DATABASE_PARAMETERS;
      
      NLS_LANGUAGE = AMERICAN
      NLS_TERRITORY = AMERICA
      NLS_CURRENCY = $
      NLS_ISO_CURRENCY = AMERICA
      NLS_NUMERIC_CHARACTERS = .,
      NLS_CHARACTERSET = AL32UTF8
      NLS_CALENDAR = GREGORIAN
      NLS_DATE_FORMAT = DD-MON-RR
      NLS_DATE_LANGUAGE = AMERICAN
      NLS_SORT = BINARY
      NLS_TIME_FORMAT = HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT = HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT = DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY = $
      NLS_COMP = BINARY
      NLS_LENGTH_SEMANTICS = BYTE
      NLS_NCHAR_CONV_EXCP = FALSE
      NLS_NCHAR_CHARACTERSET = AL16UTF16
      NLS_RDBMS_VERSION = 9.2.0.6.0
      

      I also added one TIP to the article: look at the “Edit (27 September 2012): Tips for Mockup Response…”

      • Mirjana

        Reply

        Hi Martin,
        thanks for the post. It’s helpful and it works, except…

        We have WS deployed on Glassfish that inserts into database; it is java based and uses JPA. The WS is fine, it works perfectly when called from soapUI or other client.
        But when we call the WS within PL/SQL, using UTL_HTTP we have problems with Cyrillic values.
        The problem only appears for Локација and Коментар tags, but not for all. When Cyrillic is send in the request for Предмет од Web Service tag, it works fine. With Latin values, the problem does not appear.

        We suspect that some special character is causing the problem and interrupts the soap request and the Glassfish sends exception.

        Any suggestion?

        SOAP REQUEST:

        739
        14113
        Предмет од Web Service
        12345667789101
        2012-11-23
        011/2011
        Праќа
        68

        2012-11-23
        Прима
        68

        8653
        473

        INTERNAL
        False
        False

        Локација
        Коментар
        11221122

        111
        1004979455053
        112211

        EXCEPTION IN RESPONSE:

        S:Server
        javax.xml.bind.UnmarshalException – with linked exception:
        [com.ctc.wstx.exc.WstxEOFException: Unexpected end of input block in end tag
        at [row,col {unknown-source}]: [36,15]]

        REQUEST PL/SQL code:

        UTL_HTTP.set_header (l_http_request, ‘User-Agent’, ‘Mozilla/4.0’);
        UTL_HTTP.set_header (l_http_request, ‘Host’, l_host_name || ‘:’ || l_port);
        UTL_HTTP.set_header (l_http_request, ‘Connection’, ‘close’);
        UTL_HTTP.set_header (l_http_request, ‘Content-Type’,’text/xml;charset=UTF-8′);
        UTL_HTTP.set_header (l_http_request, ‘SOAPAction’, ‘”saveDocument”‘);
        UTL_HTTP.set_header (l_http_request, ‘Content-Length’, LENGTH (l_string_request));
        UTL_HTTP.set_header (l_http_request, ‘Accept-Charset’, l_xml_encoding);

        UTL_HTTP.set_header (l_http_request, ‘Transfer-Encoding’, ‘chunked’);
        UTL_HTTP.set_body_charset (l_http_request, l_xml_encoding);

        NLS/LANG parameters of the DB are same as yours.

        • Martin Mareš

          Reply

          Sorry, but I’m not a Java developer and I have no experience with the application server Glasfish. This is probably the wrong configuration of the application server, if it works in other clients. Try to look at ENVIRONMENT variables that has Glasfish available after launch, if there is no problem in it?

  8. Ram

    Reply

    I am always getting the following Empty Response:
    Response> status_code: “302”
    Response> reason_phrase: “Redirect”
    Response> http_version: “HTTP/1.1”
    Response> length: “166”

    === Print result ===

    Result> l_response_city=
    Result> l_response_date=
    Result> l_response_temp=

    • Martin Mareš

      Reply

      You must analyze whole output to find right error. Apped this DBMS_OUTPUT… to the script:

      <<response_loop>>
      LOOP
           UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
           l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
           DBMS_OUTPUT.put_line('Buffer> "' || UTL_RAW.cast_to_varchar2(l_raw_data) || '"');
      END LOOP response_loop;
      
  9. venkat

    Reply

    hi,
    Martin Mareš

    This is venkat just want to know
    if we want to consume web service using PL/SQL then is it mandatory to expose our data base to internet
    and if we expose our data base to internet ,can we restrict to schema wise

    • Martin Mareš

      Reply

      Do not expose your DB on the internet.
      You must ensure that it was connected to the Internet from DB.
      And only need to set the correct permissions across:

      DBMS_NETWORK_ACL_ADMIN(…);

  10. venkat

    Reply

    Hi,
    Mr.Mar­tin Mareš
    Good Evevning

    Wish You a Happy New year

    This is venkat i just want to call web service Using From PL SQL
    But I Found All the samples in google which is returning only xml of one return type (UTL_HTTP.GET.RESPONSE)
    but in my case calling web service which returning blob and two more out parameters
    but using UTL_Http.Get.Response how come it is going to catch all out parameters
    Could you Please Suggest me the best way to solve the problem

    Thanks And Regards
    venkat

  11. shalu

    Reply

    Thanks Martin. You are genius. This code worked and I’m so happy for it. We’ve tried google search and various codes. It’s only your code that has worked. You are simply awesome.

  12. Shveta

    Reply

    hi i want to send a csv file as an attachment using a webservice . Can you please share a sample code for the SOAP call. The csv file is being created from a table data though I donot plan to physically store it some place.

    • Martin Mareš

      Reply

      Hy, you must send it as text encoded in base64 encoding.

  13. kiran

    Reply

    Hi Martin,
    My wsdl is http://192.168.3.215:8001/soa-infra/services/labs/Addition/AdditionService?WSDL
    I tried the following Procedure same as what you have mentioned with change respect to my l_host_name ,l_port , l_string_request;
    ————————————————————————————————-
    DECLARE
    l_http_request UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_buffer_size NUMBER(10) := 512;
    l_line_size NUMBER(10) := 50;
    l_lines_count NUMBER(10) := 20;
    l_string_request VARCHAR2(512);
    l_line VARCHAR2(128);
    l_substring_msg VARCHAR2(512);
    l_raw_data RAW(512);
    l_clob_response CLOB;
    l_host_name VARCHAR2(128) := ‘192.168.3.215’;
    l_port VARCHAR2(128) := ‘8001’;

    BEGIN
    l_string_request := ‘

    2
    1

    ‘;
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => ‘http://’ || l_host_name || ‘:’ || l_port || ‘/soa-infra/services/labs/Addition/AdditionService’, method => ‘POST’, http_version => ‘HTTP/1.1’);
    UTL_HTTP.set_header(l_http_request, ‘User-Agent’, ‘Mozilla/4.0’);
    UTL_HTTP.set_header(l_http_request, ‘Host’, l_host_name || ‘:’ || l_port);
    UTL_HTTP.set_header(l_http_request, ‘Connection’, ‘close’);
    UTL_HTTP.set_header(l_http_request, ‘Content-Type’, ‘text/xml;charset=UTF-8’);
    UTL_HTTP.set_header(l_http_request, ‘SOAPAction’, ‘”AdditionOperation”‘);
    UTL_HTTP.set_header(l_http_request, ‘Content-Length’, LENGTH(l_string_request));

    <>
    FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) – 1 LOOP
    l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);

    BEGIN
    l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
    UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT request_loop;
    END;
    END LOOP request_loop;

    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line(‘Response> status_code: “‘ || l_http_response.status_code || ‘”‘);
    DBMS_OUTPUT.put_line(‘Response> reason_phrase: “‘ ||l_http_response.reason_phrase || ‘”‘);
    DBMS_OUTPUT.put_line(‘Response> http_version: “‘ ||l_http_response.http_version || ‘”‘);

    BEGIN

    <>
    LOOP
    UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
    l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
    END LOOP response_loop;

    EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line(‘Response> length: “‘ || LENGTH(l_clob_response) || ‘”‘);
    DBMS_OUTPUT.put_line(CHR(10) || ‘=== Print first ‘ || l_lines_count || ‘ lines of HTTP response… ===’ || CHR(10) || CHR(10));

    <>
    FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) – 1 LOOP
    l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
    DBMS_OUTPUT.put_line(‘[‘ || LPAD(i, 2, ‘0’) || ‘]: ‘ || l_line);
    EXIT WHEN i > l_lines_count – 1;
    END LOOP print_response;

    IF l_http_request.private_hndl IS NOT NULL THEN
    UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
    UTL_HTTP.end_response(l_http_response);
    END IF;

    END;
    /
    —————————————————————————————————–
    But when ever I am executing this Script am getting the following output

    anonymous block completed

    I am not getting response from it,but the wsdl is triggered.Please help me to Solve this issue.

    • Martin Mareš

      Reply

      Hello, if you run script in SQL*Plus environment, you must enable DBMS_OUTPUT. There are two options how can enable DBMS_OUTPUT:

      1.) before run script you type:
      SET SERVEROUTPUT ON;

      …and run script BEGIN…END;

      2.) or in BEGIN..END block add:

      BEGIN
      dbms_output.enable(100000);
      l_string_request := … –> and the rest of the script


      END;
      /

  14. kiran

    Reply

    l_string_request := ‘

    2010-04-27

    ‘;

    this is the payload what i have used….

  15. Moataz

    Reply

    When i calling the webservice from DB and passing the parameter as HTML flag i get error .the procedure which call the webservice working fine without html tag .

  16. Ivan Jacoš

    Reply

    Hi Martin. I would like to ask for help with similar issue as already stated above regarding “HTTP server error 500 — Inter­nal Server Error” caused by encoding.

    As you proposed, I have used
    UTL_HTTP.set_header( lt_InvokeRequest, ‘Transfer-Encoding’, ‘chunked’);
    UTL_HTTP.set_body_charset( lt_InvokeRequest, l_xml_encoding);
    with
    l_xml_encoding VARCHAR2(128) := ‘UTF-8’;

    but my database NLS setting are:
    NLS_CHARACTERSET = EE8MSWIN1250
    NLS_NCHAR_CHARACTERSET = AL16UTF16
    and settings of WS estimate UTF8 strings.

    I have tried more combinations of Charset setting, also implicit conversion
    UTL_HTTP.write_raw(r => lt_InvokeRequest, data => CONVERT(l_raw_data, ‘UTF8’, ‘EE8MSWIN1250’)); … with no success. Only that worked was Czech/Slovak characters removing.

    Any suggestions?

    • Martin Mareš

      Reply

      What version of database you are using? 9i, 10g or 11 g?

  17. Dosource

    Reply

    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));

    should be replaced by

    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTHB(l_string_request));

    Otherwise, the Content-length will not be correct if you have multibytes characters in the request body.

Leave a comment

Your email address will not be published. Required fields are marked *