Call webservice from oracle database

Database Web Service

Note:

The purpose of this article is to provide a step-by-step instruction on how to install the DBWS Callout Utilities on Oracle Database Server 11g and how to verify that it works properly.

All of these environments may be placed on different machines. Anyway, it has been found convenient to have these environments on one machine for an initial installation and test of the DBWS Callout Utilities. This does rule out network problems from affecting the proper operation of the DBWS Callout Utilities.

11g Database Webservice Callout Steps

preparing the Oracle Client and Oracle Database Server

  1. Setup the appropriate environment for the Oracle Client

ORACLE_HOME = D:\app\database\product\11.2.0\dbhome_1
PATH = %PATH%;%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin

  1. Install 11g Release 2 (11.2) for Microsoft Windows (32-Bit) with Installation Type “Administrator”

This will install all the tools and jars required to work with the DBWS Callout Utilities.

  1. Drop The Package “UTL_DBWS” in Schema “SYS”. If existed

drop package UTL_DBWS;

  1. Check the OracleJVM for Invalid Classes

The schema “SYS” and the schema into which the DBWS Callout Utilities should be installed must not have invalid classes. The status of all the java classes installed on the Oracle Database Server instance can be checked as SYSDBA with following SQL statement:

SELECT owner, status, count(*) FROM DBA_OBJECTS
WHERE OBJECT_TYPE=’JAVA CLASS’
GROUP BY owner, status;

For a standard installation of Oracle Database Server 11.1 this SQL statement should return about 20000 valid classes for the owner/schema “SYS”.

It is very difficult to resolve invalid classes once they are in schema “SYS”. There is a huge number of classes in this schema and it is not easy to find out the dependencies. It is a lot easier to resolve invalid classes in schemas that are not system schemas of the Oracle Database Server, because in this case all uploaded jar files and classes with their dependencies should be known to the user. Re-compile all invalid classes( take maximum 100 ) in one compile.

The complete initialization of the OracleJVM is usually the most efficient approach to resolve problems with invalid classes in schema “SYS”, even though this is a risky and time consuming procedure.

  1. Configure the OracleJVM

Preparing the database consists mainly in loading the Web Services client stack into the database. Loading the client stack requires extra Java space inside the OracleJVM to resolve and store load jar files. Make sure the initialization parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are equal to or greater than 132M and 80M, i.e. or more

shared_pool_size=132M
java_pool_size=80M

Depending on the number of Java classes already loaded into the database, the two parameters may need to be increased beyond 132M and 80M.

In SQL*Plus you can check the size of this initialization parameters as SYSDBA:

show parameter SHARED_POOL_SIZE
show parameter JAVA_POOL_SIZE

alter system set SHARED_POOL_SIZE=132M scope=both;
alter system set JAVA_POOL_SIZE=80M scope=both;

  1. Download the Oracle DBWS Callout Utility 10.1.3.1: this with work with Oracle Database 10g & 11g both.
  2. Take a backup of %Oracle_Home%\sqlj\   folder first:
  3. Unzip the Content of the ZIP File to $ORACLE_HOME (Oracle Database Server Environment)

Unzip in %Oracle_Home%\sqlj\  dbws-callout-utility-10131.zip

  1. Modify the CLASSPATH in Clients_Home\ sqlj\bin\jpub

D:\dbhome_2\product\11.2.0\client_1\bin;D:\app\database\product\11.2.0\dbhome_2;D:\app\database\product\11.2.0\dbhome_1\bin;D:\DevSuiteHome_1\jdk\jre\bin\classic;D:\DevSuiteHome_1\jdk\jre\bin;D:\DevSuiteHome_1\jdk\jre\bin\client;D:\DevSuiteHome_1\jlib;D:\DevSuiteHome_1\bin;D:\DevSuiteHome_1\jre\1.4.2\bin\client;D:\DevSuiteHome_1\jre\1.4.2\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;D:\ORANT\BIN;%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%ORACLE_HOME%\sqlj\bin;%JAVA_HOME%\bin;D:\dbhome_2\product\11.2.0\client_1\jdk\bin

 

  1. Make JPublisher and JDK 1.5.0 available for an Oracle Client Operating System Shell

Oracle Client 11g ships with a JDK 1.5.0 already. The JDK being used for JPublisher must match with the version of the OracleJVM the generated classes will be deployed to.

e.g.:
PATH=$ORACLE_HOME\jdk\bin;%PATH %

echo %PATH%
D:\dbhome_2\product\11.2.0\client_1\bin;D:\app\database\product\11.2.0\dbhome_1;D:\app\database\product\11.2.0\dbhome_1\bin;;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%ORACLE_HOME%\sqlj\bin;%JAVA_HOME%\bin;D:\app\database\product\11.2.0\dbhome_1\sqlj\lib;

 

  1. Load the WebService Client into Oracle Database Server

loadjava -u SYS/MiracleWorker7 -r -v -f -s –grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

Output:

[oracle@sracanov-au2 lib]$ tail -8 loadjava.txt

Classes Loaded: 4061

Resources Loaded: 81

Sources Loaded: 0

Published Interfaces: 0

Classes generated: 63

Classes skipped: 0

Synonyms Created: 0

Errors: 0

[oracle@sracanov-au2 lib]$

 

Note:

The loadjava commands above load the Oracle JAX-RPC client into the target schema. By default this classes will only be available for the target user. Other users require execute privileges and synonyms for this classes, if such users should also be able to have web service callouts.

e.g.:
loadjava -u SYS/MiracleWorker7 -r -v -f -s –grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

  1. Once the database webservice call out classes have been loaded, I need to run the PL/SQL wrappers that will call these classes. The scripts are found in $ORACLE_HOME\sqlj\lib folder. Since I installed the jar in scott, scott needs to run these scripts:

Load below in sys schema.

  1. sql
  2. sql

Verify.

desc utl_dbws

  1. Here is the PL/SQL code using UTL_DBWS to call an external webservice:

CREATE OR REPLACE FUNCTION celciusToFahrenheit(temperature NUMBER) RETURN VARCHAR2 AS

service_ scott.utl_dbws.SERVICE;

call_ scott.utl_dbws.CALL;

service_qname scott.utl_dbws.QNAME;

port_qname scott.utl_dbws.QNAME;

response sys.XMLTYPE;

request sys.XMLTYPE;

BEGIN

scott.utl_dbws.set_http_proxy(‘myproxy.com:80’);

service_qname := scott.utl_dbws.to_qname(null, ‘CelciusToFahrenheit’);

service_      := scott.utl_dbws.create_service(service_qname);

call_         := scott.utl_dbws.create_call(service_);

scott.utl_dbws.set_target_endpoint_address(call_, ‘http://webservices.daehosting.com/services/TemperatureConversions.wso’);

scott.utl_dbws.set_property( call_, ‘OPERATION_STYLE’, ‘document’);

request       := sys.XMLTYPE(‘<celciustofahrenheit xmlns=”http://webservices.daehosting.com/temperature”><ncelcius>&#8217;||temperature||'</ncelcius></celciustofahrenheit>’);

response      := scott.utl_dbws.invoke(call_, request);

return response.extract(‘//CelciusToFahrenheitResult/child::text()’, ‘xmlns=”http://webservices.daehosting.com/temperature”&#8216;).getstringval();

END;

Now Test:

 

SELECT celciusToFahrenheit(30) from dual;

 

CELCIUSTOFAHRENHEIT(30)

——————————————————————————–

86

 

 

—————————————————————–

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: