Calling Web-Services from Oracle PLSQL

This with reference taken from one of my article which was published by Experts-Exchange on “Calling webservice from PLSQL” .

Web application components that can be published and used on the Internet are called web services. Here we will see how we can use Oracle PLSQL to access web services.
In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the  web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/webservices/tempconvert.asmx?op=CelsiusToFahrenheit
We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.
Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:

pic-1

Let’s put 10 as input to the parameter value for Celsius and check what output we are getting:

pic-2

After giving the input click on Invoke button to get the response as like below:

pic-3

So from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.

<string xmlns="http://www.w3schols.com/webservices/">50</string>

Now let us try to call the same web service from Oracle.
To use any web services we need to register the URL in our DB by assigning it to the Access Control List (ACL). Here is the below code which need to be executed under SYS user, so that we can utilize it based on any primary users on the schema. I am using to demonstrate this with Oracle Database 11g Release 11.2.0.1.0

 

BEGIN
        dbms_network_acl_admin.create_acl(acl         => 'www.xml'
                                         ,description => 'Test Sample ACL'
                                         ,principal   => 'EXP1'
                                         ,is_grant    => TRUE
                                         ,privilege   => 'connect');
        dbms_network_acl_admin.add_privilege(acl       => 'www.xml'
                                            ,principal => 'EXP1'
                                            ,is_grant  => TRUE
                                            ,privilege => 'resolve');
        dbms_network_acl_admin.assign_acl(acl  => 'www.xml'
                                         ,host => 'www.w3schools.com');
END;
/ 

COMMIT

/

Once we have executed the code above, we then have to allow the user to execute the UTL_HTTP package.


Connected to Oracle Database 11g Release 11.2.0.1.0
Connected as SYS

SQL>
SQL> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
  3                                      description => 'Test Sample ACL',
  4                                      principal   => 'EXP1',
  5                                      is_grant    => true,
  6                                      privilege   => 'connect');
  7 
  8    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
  9                                         principal => 'EXP1',
10                                         is_grant  => true,
11                                         privilege => 'resolve');
12 
13    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
14                                      host => 'www.w3schools.com');
15  END;
16  /

PL/SQL procedure successfully completed

SQL> COMMIT
  2  /

Commit complete

SQL>
SQL> grant execute on UTL_HTTP to EXP1;

Grant succeeded

Now we can connect to the schema and execute the below code to get the response.


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) := 'www.w3schools.com';
        l_celsius             VARCHAR2(128) := '10'; -- Celsius is passed here
        l_resp_xml            xmltype;
        l_result_xml_node     VARCHAR2(128);
        l_namespace_soap      VARCHAR2(128) := 'xmlns="<a href="http://www.w3.org/2003/05/soap-envelope&quot;';">http://www.w3.org/2003/05/soap-envelope"';</a>
        l_response_fahrenheit VARCHAR2(128);

BEGIN
        l_string_request := '<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="<a href="http://www.w3.org/2001/XMLSchema-instance&quot;">http://www.w3.org/2001/XMLSchema-instance"</a> xmlns:xsd="<a href="http://www.w3.org/2001/XMLSchema&quot;">http://www.w3.org/2001/XMLSchema"</a> xmlns:soap12="<a href="http://www.w3.org/2003/05/soap-envelope&quot;">http://www.w3.org/2003/05/soap-envelope"</a>>
  <soap12:Body>
    <CelsiusToFahrenheit xmlns="<a href="http://www.w3schools.com/webservices/&quot;">http://www.w3schools.com/webservices/"</a>>
      <Celsius>' || l_celsius || '</Celsius>
    </CelsiusToFahrenheit>
  </soap12:Body>
</soap12:Envelope>';
        utl_http.set_transfer_timeout(60);
        l_http_request := utl_http.begin_request(url          => '<a href="http://www.w3schools.com/webservices/tempconvert.asmx'">http://www.w3schools.com/webservices/tempconvert.asmx'</a>
                                                ,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;

        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 Fahrenheit value
                l_result_xml_node := '/CelsiusToFahrenheitResponse/CelsiusToFahrenheitResult';
                dbms_output.put_line('Response from w3schools webservices:');
                SELECT extractvalue(l_resp_xml
                                   ,l_result_xml_node
                                   ,'xmlns="<a href="http://www.w3schools.com/webservices/&quot;')">http://www.w3schools.com/webservices/"')</a>
                  INTO l_response_fahrenheit
                  FROM dual;
        END IF;

        dbms_output.put_line(l_celsius || ' Celsius =  ' || l_response_fahrenheit ||
                             ' Fahrenheit');

        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;
        COMMIT;
END;

Once executed, we will get the response from the web services as like below:


Connected to Oracle Database 11g Release 11.2.0.1.0
Connected as exp1

SQL> set serveroutput on
SQL> ed
SQL> /

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response from w3schools webservices:
10 Celsius =  50 Fahrenheit

PL/SQL procedure successfully completed

SQL>

So we can see from the above that we are also able to get the same result as we did accessing the URL manually.

Thanks for reading this post.

Sloba

Advertisements

9 thoughts on “Calling Web-Services from Oracle PLSQL

  1. this error, please

    ORA-29273: fallo de la solicitud HTTP
    ORA-06512: en “SYS.UTL_HTTP”, línea 1130
    ORA-12545: La conexión ha fallado porque el host destino o el objeto no existen
    ORA-06512: en línea 29

    l_http_request := utl_http.begin_request(url => ‘http://www.w3schools.com/xml/tempconvert.asmx’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s