Tip for Mockup Response

Calling Web Services in PL/SQL using UTL_HTTP package

Today we will show how to access data from Web­Ser­vices directly in PL/SQL, as I promised in the pre­vi­ous arti­cle.

[Česká verze článku — Volání Webové služby (Web­ser­vice) v PL/SQL pomocí UTL_HTTP]

For this pur­pose you need:

  1. Tool for development/testing web ser­vices. Apply­ing pack­age Soa­pUI
  2. SQL*Plus envi­ron­ment. For our tests will fully comply.

Cre­at­ing a Web­Ser­vice (for testing)

Cre­ate a test web ser­vice described by the fol­low­ing 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 pro­gram Soa­pUI. The new project will cre­ate over Soa­pUI menu item File -> New Project Soap UI.

Create new SoapUI project
Cre­ate new Soa­pUI project

Gen­er­ate Mock Ser­vice — Fig­ure shows that the WSDL doc­u­ment describes a ser­vice with one method say­Hello.

Create MOCK service...
Cre­ate MOCK service…

Mock enter a name for ser­vice — I leave the default, which offers SoapUI.

MOCK service name
MOCK ser­vice name

Start test­ing the ser­vice (if you leave the default set­tings, the ser­vice is mapped to port 8088).

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

Open and dis­play the WSDL doc­u­ment in a browser window.

Click to open browser window
Click to open browser window

Here is a browser win­dow with the result­ing WSDL doc­u­ment test that returns Web Ser­vice “HelloService”.

WSDL in Internet Explorer
WSDL in Inter­net Explorer

Run the query method say­Hello. You should see the following.

Run sayHello method
Run say­Hello method

Edit (27.9.2012): Tip for Mockup Response.
This sim­ple mod­i­fi­ca­tion can facil­i­tate Response Mockup test­ing:

Tip for Mockup Response
Run say­Hello method

Con­tent 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>

Con­tent 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>

Con­sum­ing Web­Ser­vice using the PL/SQL pack­age UTL_HTTP

We let our ser­vice Mock test run in a win­dow Soa­pUI and we get into the PL/SQL code, SQL*Plus. A pre­req­ui­site for the proper func­tion­ing of the fol­low­ing PL/SQL code is that you get from your development/testing of the Ora­cle data­base on a com­puter where you are run­ning a test web ser­vices on port 8088.

When you run the code con­sum­ing a Web ser­vice may expe­ri­ence the fol­low­ing 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 hap­pens, you need to set per­mis­sions on the con­nect call and resolve (name resolve over DNS) the Ora­cle data­base. We pro­vide the fol­low­ing script (in my case, trig­gered by the user SCOTT). In the script, please note prin­ci­pal line (which should include the name of the user for which per­mis­sions you assign), the item priv­i­lege (priv­i­lege name “con­nect” and “resolve”) and a guest entry (you can fill in either the Host­name or IP address of the com­puter is run­ning 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 per­mis­sions cor­rectly above, it should become oper­a­tional next script demon­strat­ing the Request/Response test our Web­Ser­vice. (The script, pay atten­tion to vari­ables l_host_name and l_port, val­ues 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 out­put 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>

Con­sum­ing a real Web­Ser­vice CDYNE Weather

In con­clu­sion, we show the real con­sump­tion of Web­Ser­vice CDYNE Weather (from WSDL descrip­tion), specif­i­cally a method call GetC­i­ty­Fore­cast­ByZIP. Call­ing the fol­low­ing script assumes set­tings (ACL) in an Ora­cle data­base (just as I men­tioned 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 out­put 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 thoughts on “Calling Web Services in PL/SQL using UTL_HTTP package

  1. I was just won­der­ing if I have a website(Lets take an exam­ple of any help desk URL ) and I want to login and update or com­ment on any ticket then can we use UTL_HTTP and SOAP concept…

    Like login to URL and update ticket sta­tus for a ticket number.

    Wish if we can do this in PLSQL.

    1. Yes, it can be done using the pack­age UTL_HTTP. If you use HTTPS con­nec­tions, it is more dif­fi­cult. You must first cre­ate a wrap­per for a Java func­tion that will per­form a GET/SET oper­a­tions. In Java, you must use a secu­rity provider, such as com.sun.net.ssl.internal.ssl.Provider.

      The server must have saved the cer­tifi­cate file, for exam­ple in the directory:

      /home/oracle/Wallets/truststore.file

      … and then set the fol­low­ing 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”);

      Per­haps in future I will write another arti­cle on SOAP over HTTPS

      1. I am doing HTTPS with UTL_HTTP and a Wal­let. But hav­ing trou­ble with one of the meth­ods that has an xml string input para­me­ter. Is this pos­si­bly why you ended up going the JAVA route?

  2. Martin…I am a pl sql devel­oper in USA/Virgina, try­ing to do a http post/get and retrieve xml results in the get.

    The url is a .asp url/web service.

    The post para­me­ters are in xml form and I can’t fig­ure out where they go in the code. I tested your weather procedure…worked nicely.

    Exam­ple para­me­ters are (there are many…but here show­ing just a few for for­mat below)

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

  3. Exam­ple 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

  4. Hi Mar­tin

    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 sev­eral times to get those file, I would really appre­ci­ate, if you please pro­vide me some sample.

    Regards

    Zi

    1. Hi Zi,

      If you want to to return through the WS large files, it is best to use a com­bi­na­tion of com­pres­sion + BASE64 encod­ing. By com­press­ing the out­put file first and then encode BASE64 encod­ing, you can trans­fer rel­a­tively lit­tle data. The sam­ple mes­sage 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 con­tent of the tag to file “TestFile.txt.b64“
      2. from com­mand line run: “base64 –d TestFile.txt.b64 > TestFile.txt.gz“
      3. from com­mand line run: “gun­zip TestFile.txt.gz“
      4. It orig­i­nated TestFile.txt file you should see this content:


      Lorem Ipsum is sim­ply dummy text of the print­ing and type­set­ting indus­try.
      Lorem Ipsum has been the industry’s stan­dard dummy text ever since the 1500s,
      when an unknown printer took a gal­ley of type and scram­bled it to make a type spec­i­men book.
      It has sur­vived not only five cen­turies, but also the leap into elec­tronic type­set­ting,
      remain­ing essen­tially unchanged. It was pop­u­larised in the 1960s with
      the release of Letraset sheets con­tain­ing Lorem Ipsum pas­sages,
      and more recently with desk­top pub­lish­ing soft­ware
      like Aldus Page­Maker includ­ing ver­sions of Lorem Ipsum.

      1. Hi Mar­tin

        Thanks for your reply. I really appre­ci­ate this.

        My sit­u­a­tion is lit­tle dif­fer­ent. I am call­ing UCM (Ora­cle Con­tent Man­age­ment) to get a file. This file could be pdf/doc/excel and file size could dif­fer. I am using a soap call which returns some­thing like below (pls see reponse). I need to get the file from below soap response. I am call­ing wsdl from APEX (Pl/sql) and need to down­load this file from a web link. I need to use pl/sql (since it is apex client) to call the wsdl. I would appre­ci­ate you any help.

        Exam­ple
        =======

        FY2020File Down­load Link

        Request
        =======

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

        Response
        ========

        FY12-0149_MISC_14012
        GetFile.wsdl
        Doc­u­ment
        GM
        Public

        9003
        8803
        1
        RELEASED

        xxx.doc
        PD94bWwgdmVyc2lv…base64data…+DQo=

        0
        You are logged in as ‘test

        I would really appre­ci­ate your any help.

        Zi

          1. Hi Mar­tin

            Really appre­ci­ate your time and good inten­tion to help me our.

            I just need help a sam­ple exam­ple to down­load a file(msword/excle etc) from a web link using Pl/Sql. Even though I am using APEX, but that has very min­i­mum effect on this, since I am using pure Pl/Sql.

            Another ques­tion, in the response I am get­ting file­Con­tent tag with value on it. Is that is the thing I need extract? how do I con­vert it to actual file using pl/sql?

            I would really appre­ci­ate your help.

            Thanks/Zi

            test_test.pptx
            idc:fileContent>UEsDBBQABgAIAAAAIQDfzBj1wgEAAEYMAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAAAAAAAAAAAAAAAAAA

  5. Hi Mar­tin,
    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 con­tent type as multipart/form-data.

    Thanks

  6. Dear Mar­tin,

    When I send SOAP enve­lope with ”Mar­tin Mareš” got error:

    ORA-29269: HTTP server error 500 — Inter­nal Server Error

    ORA-06512: at line 9

    But when I send ENVELOPE with ”Mar­tin Mares” every­thing is fine???

    I see here that encod­ing is UTF-8, but still have problems.

    Do you have this problems???

    1. Hello Edin, It was a chal­lenge for me :-)

      The key is to add the fol­low­ing 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 com­plete mod­i­fi­ca­tion of the sam­ple 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 for­get that it also depends on the NLS_LANG* para­me­ter set­ting 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 arti­cle: look at the “Edit (27 Sep­tem­ber 2012): Tips for Mockup Response…”

      1. Hi Mar­tin,
        thanks for the post. It’s help­ful and it works, except…

        We have WS deployed on Glass­fish that inserts into data­base; it is java based and uses JPA. The WS is fine, it works per­fectly when called from soa­pUI or other client.
        But when we call the WS within PL/SQL, using UTL_HTTP we have prob­lems with Cyril­lic val­ues.
        The prob­lem only appears for Локација and Коментар tags, but not for all. When Cyril­lic is send in the request for Предмет од Web Ser­vice tag, it works fine. With Latin val­ues, the prob­lem does not appear.

        We sus­pect that some spe­cial char­ac­ter is caus­ing the prob­lem and inter­rupts the soap request and the Glass­fish sends exception.

        Any sug­ges­tion?

        SOAP REQUEST:

        739
        14113
        Предмет од Web Ser­vice
        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 excep­tion:
        [com.ctc.wstx.exc.WstxEOFException: Unex­pected 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, ‘Con­nec­tion’, ‘close’);
        UTL_HTTP.set_header (l_http_request, ‘Content-Type’,‘text/xml;charset=UTF-8′);
        UTL_HTTP.set_header (l_http_request, ‘SOA­PAction’, ’”save­Doc­u­ment”’);
        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’, ‘chun­ked’);
        UTL_HTTP.set_body_charset (l_http_request, l_xml_encoding);

        NLS/LANG para­me­ters of the DB are same as yours.

        1. Sorry, but I’m not a Java devel­oper and I have no expe­ri­ence with the appli­ca­tion server Glas­fish. This is prob­a­bly the wrong con­fig­u­ra­tion of the appli­ca­tion server, if it works in other clients. Try to look at ENVIRONMENT vari­ables that has Glas­fish avail­able after launch, if there is no prob­lem in it?

  7. I am always get­ting the fol­low­ing Empty Response:
    Response> status_code: “302”
    Response> reason_phrase: “Redi­rect“
    Response> http_version: “HTTP/1.1″
    Response> length: “166”

    === Print result ===

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

    1. You must ana­lyze whole out­put 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;
      
  8. hi,
    Mar­tin Mareš

    This is venkat just want to know
    if we want to con­sume web ser­vice using PL/SQL then is it manda­tory to expose our data base to inter­net
    and if we expose our data base to inter­net ‚can we restrict to schema wise

    1. Do not expose your DB on the inter­net.
      You must ensure that it was con­nected to the Inter­net from DB.
      And only need to set the cor­rect per­mis­sions across:

      DBMS_NETWORK_ACL_ADMIN(…);

  9. Hi,
    Mr.Mar­tin Mareš
    Good Evevning

    Wish You a Happy New year

    This is venkat i just want to call web ser­vice Using From PL SQL
    But I Found All the sam­ples in google which is return­ing only xml of one return type (UTL_HTTP.GET.RESPONSE)
    but in my case call­ing web ser­vice which return­ing blob and two more out para­me­ters
    but using UTL_Http.Get.Response how come it is going to catch all out para­me­ters
    Could you Please Sug­gest me the best way to solve the problem

    Thanks And Regards
    venkat

  10. Thanks Mar­tin. You are genius. This code worked and I’m so happy for it. We’ve tried google search and var­i­ous codes. It’s only your code that has worked. You are sim­ply awesome.

  11. hi i want to send a csv file as an attach­ment using a web­ser­vice . Can you please share a sam­ple code for the SOAP call. The csv file is being cre­ated from a table data though I donot plan to phys­i­cally store it some place.

  12. Hi Mar­tin,
    My wsdl is http://192.168.3.215:8001/soa-infra/services/labs/Addition/AdditionService?WSDL
    I tried the fol­low­ing Pro­ce­dure same as what you have men­tioned 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, ‘Con­nec­tion’, ‘close’);
    UTL_HTTP.set_header(l_http_request, ‘Content-Type’, ‘text/xml;charset=UTF-8′);
    UTL_HTTP.set_header(l_http_request, ‘SOA­PAction’, ’”Addi­tion­Op­er­a­tion”’);
    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 exe­cut­ing this Script am get­ting the fol­low­ing output

    anony­mous block completed

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

    1. Hello, if you run script in SQL*Plus envi­ron­ment, 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 BEGINEND;

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

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


      END;
      /

  13. When i call­ing the web­ser­vice from DB and pass­ing the para­me­ter as HTML flag i get error .the pro­ce­dure which call the web­ser­vice work­ing fine with­out html tag .

  14. Hi Mar­tin. I would like to ask for help with sim­i­lar issue as already stated above regard­ing “HTTP server error 500 — Inter­nal Server Error” caused by encoding.

    As you pro­posed, I have used
    UTL_HTTP.set_header( lt_InvokeRequest, ‘Transfer-Encoding’, ‘chun­ked’);
    UTL_HTTP.set_body_charset( lt_InvokeRequest, l_xml_encoding);
    with
    l_xml_encoding VARCHAR2(128) := ‘UTF-8′;

    but my data­base NLS set­ting are:
    NLS_CHARACTERSET = EE8MSWIN1250
    NLS_NCHAR_CHARACTERSET = AL16UTF16
    and set­tings of WS esti­mate UTF8 strings.

    I have tried more com­bi­na­tions of Charset set­ting, also implicit con­ver­sion
    UTL_HTTP.write_raw(r => lt_InvokeRequest, data => CONVERT(l_raw_data, ‘UTF8’, ‘EE8MSWIN1250’)); … with no suc­cess. Only that worked was Czech/Slovak char­ac­ters removing.

    Any sug­ges­tions?

  15. 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));

    Oth­er­wise, the Content-length will not be cor­rect if you have multi­bytes char­ac­ters in the request body.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>