Category Archives: dataIntegration

11g database web services

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.

Setting up 11g Streams Schema replication

Setup Two databases

Use DBCA to create two 11g databases on the same server or on two different servers

In this sample let us assume that we are using databases RK01 and RK02

Setup the streams related initialization parameters in both databases

Since it is simpler to set parameters up in a pfile as opposed to a spfile, use the create pfile from spfile command to create pfiles on both databases.

On RK01 add the following lines to the pfile

# Added for streams
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1=’LOCATION=/u01/app/orarch/RK01 MANDATORY’
*.log_archive_dest_state_1=ENABLE
*.log_archive_format=’RK01_%t_%s_%r.arc’
*.parallel_max_servers=12
*.processes=100
*.service_names=’RK01′
*.streams_pool_size=20

Add the following lines to the pfile for RK02

#Added for streams
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1=’LOCATION=/u01/app/orarch/RK02 MANDATORY’
*.log_archive_dest_state_1=ENABLE
*.log_archive_format=’RK02_%t_%s_%r.arc’
*.parallel_max_servers=12
*.processes=100
*.service_names=’RK02′
*.streams_pool_size=20

use the hr_main script ($ORACLE_HOME/demo/schema/human_resources) to create the hr schema in your first database (RK01). Do not create this schema in RK02, since it will get created when you run the streams schema maintenance script

Login as sys into RK01 and create a directory audit_dir pointing to /tmp/

Create the streams administrator in both databases

GRANT DBA TO strmadmin IDENTIFIED BY manager;

ALTER USER strmadmin DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

Create database links in both databases (As strmadmin)

On RK01

CREATE DATABASE LINK rk02 CONNECT TO strmadmin
IDENTIFIED BY manager USING ‘rk02’;

On RK02

CREATE DATABASE LINK RK01 CONNECT TO strmadmin
IDENTIFIED BY manager USING ‘RK01’;

Create tnsnames.ora entries to provide connectivity between the databases

Start the database with the new pfile (That has the streams related parameters set)

Set the databases to be in Archivelog mode

Setup Streams

Use DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS to create a script to setup schema replication

BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => ‘hr’,
source_directory_object => ‘audit_dir’,
destination_directory_object => ‘audit_dir’,
source_database => ‘RK01’,
destination_database => ‘RK02’,
capture_name => ‘capture_rk01’,
capture_queue_table => ‘rep_capture_queue_table’,
capture_queue_name => ‘rep_capture_queue’,
capture_queue_user => null,
apply_name => ‘apply_rk01’,
apply_queue_table => ‘rep_dest_queue_table’,
apply_queue_name => ‘rep_dest_queue’,
apply_queue_user => null,
propagation_name => ‘prop_rk01’,
log_file => ‘exp.log’,
bi_directional => true,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network,
perform_actions => false,
script_name => ‘schema_replication_ii.sql’,
script_directory_object => ‘audit_dir’
);
END;

Login as sys on RK01 and run the script

Test replication