Many applications today are architect ed and built to conform with the principles of Service Oriented Architecture . When implementing SOA, many loosly coupled services are built and are orchestrated to work together to deliver a business function. The services can be built using any programming language, like c#, java, perl etc. Usually the programs that consume the services (Or services that consume other services), communicate with the service using the standard protocol named SOAP (Simple Object Access Protocol) . SOAP messages use standard XML messages that conform with the SOAP specification.
The description of a service, the location of the service and the operations the service exposes are exposed in form of an XML document named WSDL. Typically the deployment of services, use an Application server like Oracle Fusion middleware, Jboss etc.
In oracle 11g database, you can create and deploy web services right from the database, without really using any application server. The steps to accomplish this in an 11.1.0.7 database (should work in 11.1.0.6 too) are below. This is all possible because XMLDB in the database, comes with a protocol server, which supports the HTTP(S) protocol. So we can enable HTTP access to the database.
Setup the http port
Login as sys to the database
EXEC dbms_xdb.sethttpport(8080);
Configure the web services servlet
DECLARE
SERVLET_NAME VARCHAR2(32) := ‘orawsv’;
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME => SERVLET_NAME,
LANGUAGE => ‘C’,
DISPNAME => ‘Oracle Query Web Service’,
DESCRIPT => ‘Servlet for issuing queries as a Web Service’,
SCHEMA => ‘XDB’);
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => ‘XDB_WEBSERVICES’,
ROLELINK => ‘XDB_WEBSERVICES’);
DBMS_XDB.addServletMapping(PATTERN => ‘/orawsv/*’,
NAME => SERVLET_NAME);
END;
/
You can run the following query from sqlplus to determine if the configuration was successful
XQUERY declare default element namespace “http://xmlns.oracle.com/xdb/xdbconfig.xsd”;
Create the User that will own the pl/sql procedures that will be used as the web services.
create user rk01srv identified by rk01srv
quota unlimited on users;
grant connect,create table,create procedure to rk01srv;
GRANT XDB_WEBSERVICES TO rk01srv;
GRANT XDB_WEBSERVICES_OVER_HTTP TO rk01srv;
Create the table and Pl/Sql procedure
Drop table Books
/
Create Table Books
(
Isbn Varchar2(15),
BookName Varchar2(100),
Author Varchar2(100)
)
/
alter table Books add constraints pk_books primary key (Isbn)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Learning Perl’,’0596004788′,’Randal L. Schwartz’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Perl Cookbook’,’1565922433′,’Tom Christiansen’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Programming perl’,’0937175641′,’Larry Wall’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Agile Web Development with Rails’,’1934356166′,’Sam Ruby’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Advanced Rails Recipes’,’0978739221′,’Mike Clark’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Code Complete’,’0735619670′,’Steve McConnell’)
/
Insert into Books (BookName,Isbn,Author) Values (
‘Art of Computer Programming Volume 1′,’0201038013’,’Donald E Knuth)
/
CREATE OR REPLACE PROCEDURE get_name (
p_isbn IN Books.isbn%TYPE,
p_bookname OUT Books.BookName%TYPE) AS
BEGIN
SELECT BookName
INTO p_bookname
FROM Books
WHERE isbn = p_isbn;
END;
/
Test access to the service
Invoke your browser and type in the following URL to display the WSDL for the service named Get_Name
http://localhost:8080/orawsv/RK01SRV/GET_NAME?wsdl
You will be prompted for the username and password. This is the database username and password. Enter rk01srv for the username and password.
Create a perl script that calls the service
You need to have installed the perl library SOAP::Lite. This library has methods you can use to generate SOAP calls to web services.
The full script can be found belowCreate a perl script that calls the service
#!/usr/bin/perl
#use SOAP::Lite;
use SOAP::Lite ;
$l_isbn = shift;
sub SOAP::Transport::HTTP::Client::get_basic_credentials {
return ‘rk01srv’ => ‘rk01srv’;
}
print “The response from the server was: “;
print SOAP::Lite
->uri(‘http://localhost:8080/orawsv/RK01SRV/GET_NAME’)
->proxy(‘http://localhost:8080/orawsv/RK01SRV/GET_NAME’)
->call (SOAP::Data->name(“GET_NAMEInput”),SOAP::Data->name(“P_ISBN-VARCHAR2-IN” => $l_isbn),SOAP::Data->name(“P_BOOKNAME-VARCHAR2-OUT” => “”))
->result;
The method ” SOAP::Transport::HTTP::Client::get_basic_credentials”, from the SOAP::Lite library is used to setup the username and password to be passed to the SOAP call.
You have to specify the URL for the soap service and the proxy name for the soap service (The theory being that one proxy can host multiple services). Then you specify the service name (SOAP::Data->name(“GET_NAMEInput”))and each of the arguments (SOAP::Data->name(“P_ISBN-VARCHAR2-IN” => $l_isbn),SOAP::Data->name(“P_BOOKNAME-VARCHAR2-OUT” => “”)), exactly as the WSDL displayed those element names (Note that the names are different from the actual procedure name and the argument names we defined in Pl/Sql).
Then you can run the perl script as following
./getbook.pl “0201038013”
It will print out the book name that matches that ISBN code.
So using web services in the database is a good way of creating multiple loosely coupled services which can then be accessed by web based applications. The good part is that we just had to write the logic in Pl/Sql, the database took care of enabling the web service for the procedure. So this reduces a lot of work.
Cautionary note : In the example above, I use http for the protocol. This means that the database username and password you provide is transmitted to the web server in clear text, which means that someone could sniff the network and read your packets and get your username and password. So you should consider using https instead.
