Oracle Web Services

How to call web services in Oracle?

In this Post we would be looking into a basic example for a calling a web service from the database

Oracle provides a set of packages to access the web services directly through PL/SQL.

UTL_HTTP is a standard package given by Oracle to consume a web service.

From Oracle database we can directly call the web services using PL/SQL.

Steps

1. View the SOAP envelope of the web service to be consumed.

We have a lot of free web services available over the internet, I have chosen

a free to web service to validate a given emailid

Validate email id

Click the above link to test the web service and to get the full details of the web services.

2. How the input needs to be send to a web service?

All the communications over the internet is done through XML. So we have to create a XML fragment.

xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

{ All the request details are sent as an XML imput email address

}

Note

To have a complete description of what is SOAP and why it needs to be used kindly click the following link

SOAP

3. Create a PL/SQL procedure to create the input to be given in the web service.

CREATE OR REPLACE
FUNCTION F_VALIDATE_EMAIL
(
p_email_id VARCHAR2)
RETURN VARCHAR2
AS
input_envelope VARCHAR2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
resp sys.xmltype;
in_xml sys.xmltype;
WSDL_URL VARCHAR2(2000):='http://www.webservicex.net/ValidateEmail.asmx?WSDL';
res      VARCHAR2(1000);
BEGIN
-- Generating the input to be sent to the web service
input_envelope := '<?xml version="1.0" encoding="utf-8"?>                
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">                 
<soap:Body>                   
<IsValidEmail xmlns="http://www.webservicex.net">                      
<Email>'||p_email_id||'</Email>                   
</IsValidEmail>                 
</soap:Body>                
</soap:Envelope>';
--Sending the input enevlope to the web service
http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope));
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');
utl_http.write_text(http_req, input_envelope);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, input_envelope);
res := REPLACE(input_envelope,'<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">','');
res := REPLACE(res,'<soap:Body><IsValidEmailResponse xmlns="http://www.webservicex.net"><IsValidEmailResult>','');
res := REPLACE(res,'</IsValidEmailResult></IsValidEmailResponse></soap:Body></soap:Envelope>','');
dbms_output.put_line(SUBSTR(res,1,250));
utl_http.end_response(http_resp);
RETURN res;
END;

Now let us dissect the code
1. This portion of the code is used for creating the input to the web service

-- Generating the input to be sent to the web service
input_envelope := '<?xml version="1.0" encoding="utf-8"?>                
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">                 
<soap:Body>                   
<IsValidEmail xmlns="http://www.webservicex.net">                      
<Email>'||p_email_id||'</Email>    --p_email_id is sent as a parameter                
</IsValidEmail>                 
</soap:Body>     

The format for the SOAP envelope would be aldready given in the service description of the web service.You need to replace only the parameter to be passed.

2. Setting all the configurations before calling the web service

http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1'); -- Starts the request
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); --Setting the content type
utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope)); --Setting the length
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail'); --Setting the function to be called

3. Calling the web service and retrieving the value

 utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');
utl_http.write_text(http_req, input_envelope);
http_resp := utl_http.get_response(http_req); --Getting the response for the given SOAP enevelope
utl_http.read_text(http_resp, input_envelope); -- Getting response in a variable

Note
As the repsone given by the web services is a XML fragment, use a XMLTYPE variable to manipulate the output given by the web service to retrieve the desired value
i.e
The response given by the web service would be as follows

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <IsValidEmailResponse xmlns="http://www.webservicex.net">
   <IsValidEmailResult>true</IsValidEmailResult>
  </IsValidEmailResponse>
 </soap:Body>
</soap:Envelope>

How to Create external tables in Oracle

                                             ORACLE EXTERNAL TABLES
This post summarizes the steps to create an external  table in Oracle.

Note

    The following changes should be done in the database server workstation

Overview

1. Create a directory

2.  Give apropriate access to the accessing user

3.  Create the csv file which need to be accessed 

4.  Create an external table 

5. Try selecting the record using the external table

     

Steps to be followed in creating an external table

1. Create a Directory 

     1. Log in to a schema using sql * plus

     2. You would have placed the csv file in a folder in you local machine

          I have placed the csv file in H:\FORMS folder in my local system.

  So I would be running the following statement in SQL * PLUS.

          create or replace directory forms as 'H:\Forms';

          

2.  Give appropriate access to the accessing user

         We need to give the appropriate acess to all the users who is going 

         to access the following  directory

        grant read, write on directory forms to store;        

         

3.  Create the csv file which need to be accessed 

     I have created a file named Family.csv and placed it in the H:\Forms folder in my local.

     It looks similiar to the  following file.

         

 4.  Create an external table 

      We need to create a table to access the records available in the CSV file.

      The syntax for creating an external table is as follows.

        create TABLE family

  (

     ID number,  -- Use VARCHAR2 eventhough you are using a NUMBER column

     name varchar2(100),

       comments varchar2(1000)

      )

   organization external (

   type   oracle_loader

   default directory FORMS

   access parameters (

       records delimited  by newline

       fields  terminated by ','

     missing field values are null

    )

     location ('Family.csv')

     )

   reject limit unlimited

          /

         We would be getting the following output in SQL * PLUS

           

         

5. Try selecting the record using the external table

      Select * from family;

      The output is as follows in SQL * PLUS.