<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Calling webservice from PLSQL

Published on
32,965 Points
28,265 Views
2 Endorsements
Last Modified:
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.jpgLet's put 10 as input to the parametrer value for Celsius and check what output we are getting:

pic-2.jpgAfter giving the input click on Invoke button to get the response as like below:

pic-3.jpgSo 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>

Open in new window


Now let us try to call the same webservice 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
/

Open in new window


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

Open in new window


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="http://www.w3.org/2003/05/soap-envelope"';
        l_response_fahrenheit 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>
    <CelsiusToFahrenheit xmlns="http://www.w3schools.com/webservices/">
      <Celsius>' || l_celsius || '</Celsius>
    </CelsiusToFahrenheit>
  </soap12:Body>
</soap12:Envelope>';
        utl_http.set_transfer_timeout(60);
        l_http_request := utl_http.begin_request(url          => 'http://www.w3schools.com/webservices/tempconvert.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;

        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="http://www.w3schools.com/webservices/"')
                  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;

Open in new window


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> 

Open in new window


So we can see from the above that we are also able to get the same result as we did accessing the URL manually. 
2
Author:Swadhin Ray
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month