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 for converting Celsius to Fahrenheit:
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:


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


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


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

<string xmlns="">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


        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 => '');



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
Connected as SYS

  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');
  8    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
  9                                         principal => 'EXP1',
10                                         is_grant  => true,
11                                         privilege => 'resolve');
13    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
14                                      host => '');
15  END;
16  /

PL/SQL procedure successfully completed

  2  /

Commit complete

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.

        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) := '';
        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=";';">"';</a>
        l_response_fahrenheit VARCHAR2(128);

        l_string_request := '<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="<a href=";">"</a> xmlns:xsd="<a href=";">"</a> xmlns:soap12="<a href=";">"</a>>
    <CelsiusToFahrenheit xmlns="<a href=";">"</a>>
      <Celsius>' || l_celsius || '</Celsius>
        l_http_request := utl_http.begin_request(url          => '<a href="'">'</a>
                                                ,method       => 'POST'
                                                ,http_version => 'HTTP/1.1');
                           ,'application/soap+xml; charset=utf-8');

        FOR i IN 0 .. ceil(length(l_string_request) / l_buffer_size) - 1
                l_substring_msg := substr(l_string_request
                                         ,i * l_buffer_size + 1
                        l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
                        utl_http.write_raw(r    => l_http_request
                                          ,data => l_raw_data);
                        WHEN no_data_found THEN
                                EXIT request_loop;
        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 || '"');

                        l_clob_response := l_clob_response ||
                END LOOP response_loop;
                WHEN utl_http.end_of_body THEN

        IF (l_http_response.status_code = 200)
                -- Create XML type from response text
                l_resp_xml := xmltype.createxml(l_clob_response);
                -- Clean SOAP header
                SELECT extract(l_resp_xml
                  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
                                   ,'xmlns="<a href=";')">"')</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
        END IF;

        IF l_http_response.private_hndl IS NOT NULL
        END IF;

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

Connected to Oracle Database 11g Release
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


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.



12 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 => ‘’

  2. Hi,

    First of all, thanks for sharing!!!

    I’m testing the anonymous block but it seems like there are some lines to correct, for instance:
    Line 17

    l_namespace_soap VARCHAR2(128) := ‘xmlns=”“‘;

    It finish without a single quote (‘).

    Might you check it again please?


  3. Hi

    after executing dbms_network_acl_admin.create_acl(acl => ‘www.xml’,description => ‘Test Sample ACL’,principal => ‘EXP1’,is_grant => TRUE,privilege => ‘connect’); i have the following errros :

    [Error] Execution (2: 9): ORA-06550: line 2, column 9:
    PLS-00201: identifier ‘DBMS_NETWORK_ACL_ADMIN’ must be declared
    ORA-06550: line 2, column 9:
    PL/SQL: Statement ignored
    ORA-06550: line 3, column 9:
    PLS-00201: identifier ‘DBMS_NETWORK_ACL_ADMIN’ must be declared
    ORA-06550: line 3, column 9:
    PL/SQL: Statement ignored
    ORA-06550: line 4, column 9:
    PLS-00201: identifier ‘DBMS_NETWORK_ACL_ADMIN’ must be declared
    ORA-06550: line 4, column 9:
    PL/SQL: Statement ignored

    What is the solution please?

    • It seems the package is not installed, can you check if the DBMS_NETWORK_ACL_ADMIN is available on your database or not if not then under administrator account go to oracle_home/rdbms/admin/ path and check for dbmsnacl.sql file for installing it and once completed try it again, should work.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s