11g Database Webservice Callout Steps

Posted by Steve Racanovic | Posted in , | Posted on 10:02 AM

0

11g Database Webservice Callout Steps

I installed the 11g R2 database last week and though I'd go through notes 469588.1, 428775.1 and 841183.1 on My Oracle Support and jot down my steps for configuring UTL_DBWS and JPublisher to use a java webservice proxy from the database to call external webservices.

This is what I done:

1. Check to see if the database webservice utilities are installed in sys schema, check the status of the java classes and set the OJVM pool size.

a. Check and see if callout utilities are installed:

[oracle@sracanov-au2 dbhome_1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 14 08:04:09 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn sys/ as sysdba
Enter password:
Connected.

SQL> desc sys.utl_dbws
ERROR:
ORA-04043: object sys.utl_dbws does not exist
b. Check the status of the java classes:
SQL> SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status;

OWNER                          STATUS    COUNT(*)
------------------------------ ------- ----------
MDSYS                          VALID          531
SYS                            VALID        20444
EXFSYS                         VALID           47
ORDSYS                         VALID         1898
c. Set the OJVM pool size. I'm setting the size as per note 469588.1. Although the online documentation suggest java_pool_size is set to at least 96 MB and shared_pool_size is set to at least 80 MB. http://download.oracle.com/docs/cd/E11882_01/java.112/e10587/intro.htm#BHCBACII. If this is not set, then loading and resolving java classes (jars) will occur errors:
SQL> show parameter SHARED_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> show parameter JAVA_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0
SQL> alter system set SHARED_POOL_SIZE=132M scope=both;

System altered.

SQL> alter system set JAVA_POOL_SIZE=80M scope=both;

System altered.

SQL> show parameter SHARED_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 132M
SQL> show parameter JAVA_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 80M
SQL>
2. Download JPublisher & Database Web Services Callout Utilities

Webpage:
http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
Download - JPublisher 10g Release 10.2:
http://download.oracle.com/otn/utilities_drivers/jdbc/10201/jpub_102.zip

Webpage:
http://www.oracle.com/technetwork/database/enterprise-edition/index-097123.html
Download - 10.1.3.1 Callout Utility for 10g and 11g RDBMS (ZIP, ~13MB):
http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

3. Setup the OS environment:
[oracle@sracanov-au2 ~]$ export ORACLE_HOME=/u01/programs/oracle/product/11.2.0/dbhome_1
[oracle@sracanov-au2 ~]$ export JAVA_HOME=$ORACLE_HOME/jdk
[oracle@sracanov-au2 ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/sqlj/bin:$JAVA_HOME/bin:$PATH
[oracle@sracanov-au2 ~]$ export CLASSPATH=$ORACLE_HOME/sqlj/lib/translator.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/sqlj/lib/dbwsa.jar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jdk/lib/dt.jar:$ORACLE_HOME/jdk/lib/tools.jar:$ORACLE_HOME/jlib/jssl-1_1.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aqapi.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.jar
I extract the downloaded files in the next setup. You can set the classpath now or later. Doing it now for simplicity.

4. Extract the downloaded files:

a. Take a backup of sqlj folder first:
[oracle@sracanov-au2 ~]$ mv $ORACLE_HOME/sqlj $ORACLE_HOME/sqlj.org
b. Extract Jpub:
[oracle@sracanov-au2 ~]$ unzip jpub_102.zip -d $ORACLE_HOME
Archive:  jpub_102.zip
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/sqljutl.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/translator.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/runtime12.jar
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/README.txt
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub.c
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub.exe
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/README.txt
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Booleans.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Indexby.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Inherit.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalO8i.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/PlsqlType.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/README.txt
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Rational.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestBooleans.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestCallin.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestIndexby.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInh.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstall.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstallCreateTable.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstallJDBC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestQuery.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalO8i.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestPlsqlType.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestRationalP.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/WrDbmsUtil.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/connect.properties
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/jpub.properties
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TemperatureService.wsdl
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/doc/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/doc/faq.html
[oracle@sracanov-au2 ~]$
c. Extract Callout Utilities:
[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip sqlj\* -d $ORACLE_HOME
Archive:  dbws-callout-utility-10131.zip
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/utl_dbws_decl.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/utl_dbws_body.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientws.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb102.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb11.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsa.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb101.jar
[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip samples\* -d $ORACLE_HOME/sqlj
Archive:  dbws-callout-utility-10131.zip
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/javacallout.wsdl
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/test-plsql-dii.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/javacallout.ear
[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip *.htm -d sqlj/
Archive:  dbws-callout-utility-10131.zip
  inflating: sqlj/10.htm
  inflating: sqlj/11.htm
  inflating: sqlj/README.htm
d. Looking at the extracted files, there is no dbwsclient.jar.

http://download.oracle.com/docs/cd/E11882_01/java.112/e10587/intro.htm#BHCBACII

There is however dbwsclientws.jar. dbwsclientws.jar is the same as dbwsclient.jar. Jpub looks for this file when you run the jpub command. To avoid the error: 'Error loading dbwsclient.jar, please check the database trace file and make sure dbwsclient.jar exists in ORACLE_HOME/sqlj/lib.' when running the jpub command, I'll just make a copy of this file:
[oracle@sracanov-au2 lib]$ ls -al | grep dbwsc
-rw-r--r--  1 oracle oinstall  737386 Jun 18  2008 dbwsclientdb101.jar
-rw-r--r--  1 oracle oinstall  208486 Jun 18  2008 dbwsclientdb102.jar
-rw-r--r--  1 oracle oinstall  214156 Jun 18  2008 dbwsclientdb11.jar
-rw-r--r--  1 oracle oinstall 6716174 Aug  1  2008 dbwsclientws.jar
[oracle@sracanov-au2 lib]$ cp dbwsclientws.jar dbwsclient.jar
[oracle@sracanov-au2 lib]$ ls -al | grep dbwsc
-rw-r--r--  1 oracle oinstall  737386 Jun 18  2008 dbwsclientdb101.jar
-rw-r--r--  1 oracle oinstall  208486 Jun 18  2008 dbwsclientdb102.jar
-rw-r--r--  1 oracle oinstall  214156 Jun 18  2008 dbwsclientdb11.jar
-rw-r--r--  1 oracle oinstall 6716174 Dec 16 06:42 dbwsclient.jar
-rw-r--r--  1 oracle oinstall 6716174 Aug  1  2008 dbwsclientws.jar
[oracle@sracanov-au2 lib]$
I am not sure why they packaged it like this. It seems it was packaged dbwsclient.jar in the previous download version i.e. 10.1.3.0 Callout Utility for 10g RDBMS (ZIP, 6.89MB).

Note that I did not find this step crucial. I could still run jpub and each time I will get that error above 'Error loading dbwsclient.jar,..... It could still proceed without it and it worked fine in my example. However doing this I avoid seeing that error all the time when I run Jpub.

5. Set the execute permission on jpub:
[oracle@sracanov-au2 ~]$ cd $ORACLE_HOME/sqlj/bin
[oracle@sracanov-au2 bin]$ chmod +x jpub
6. I'm using the scott schema in this example. Check the schema has CONNECT, RESOURCE and CREATE PUBLIC SYNONYM grants. This is the minimum permission required. If not, allow for it.
SQL> SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
SCOTT                          RESOURCE
SCOTT                          CONNECT

SQL> select * from dba_sys_privs where GRANTEE='SCOTT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO

SQL> grant CREATE PUBLIC SYNONYM to scott;
SQL> select * from dba_sys_privs where GRANTEE='SCOTT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO
SCOTT                          CREATE PUBLIC SYNONYM                    NO
7. Load the database webservice callout utilities into the scott schema. The jar's are found in $ORACLE_HOME/sqlj/lib folder.
[oracle@sracanov-au2 bin]$ cd $ORACLE_HOME/sqlj/lib
[oracle@sracanov-au2 lib]$ loadjava -u scott/tiger -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar >& loadjava.txt
Check the log and see if it completed successfully. It should look something like:
[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]$
(The values are likely to be different. This depends the on system environment and what already installed).

8. Once the database webservice call out classes have been loaded, I need to run the PL/SQL wrappers that will call theses classes. The scripts are found in $ORACLE_HOME/sqlj/lib folder. Since I installed the jar in scott, scott needs to run these scripts:
[oracle@sracanov-au2 lib]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 15 11:08:50 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utl_dbws_decl.sql

Package created.

SQL> @utl_dbws_body.sql

Package body created.

Grant succeeded.

SQL> desc utl_dbws
PROCEDURE ADD_PARAMETER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CALL_HANDLE                    NUMBER                  IN
 XML_NAME                       VARCHAR2                IN
 Q_NAME                         VARCHAR2(4096)          IN
...
...
9. Grant the following classes from the SYS schema to scott. (or whichever schema your using. Ensure the schema name is in capital letters. i.e. 'SCOTT'):
SQL> conn /as sysdba
Connected.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
EXAMPLE 1:
==========
10. Using the example code from note 841183.1, I will now use the UTL_DBWS package loaded in scott to call the webservice. The PL/SQL is created as follows:

a. 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(''||temperature||'');
response      := scott.utl_dbws.invoke(call_, request);
return response.extract('//CelciusToFahrenheitResult/child::text()', 'xmlns="http://webservices.daehosting.com/temperature"').getstringval();
END;
/
show errors;
b. I have changed line 11 with my proxy server details. If your environment does not use a proxy server leave this line out.
c. Since the database webservice utilities have been installed in 'scott', I have changed all the 'sys' references to webservice utilities to 'scott'
d. Ensure you can access the webservice used in this example: http://webservices.daehosting.com/services/TemperatureConversions.wso
e: Now run the example:
SQL> @celciusToFahrenheit.sql

Function created.

No errors.
SQL> SELECT celciusToFahrenheit(30) from dual;

CELCIUSTOFAHRENHEIT(30)
--------------------------------------------------------------------------------
86

SQL>
EXAMPLE 2:
==========
11. Now using the same webservice, I will use jpub to create and load the java webservice proxy classes to the database.

a. Run the jpub to create and load your java webservice client proxy. Here is the command used:

jpub -u scott/tiger -sysuser sys/welcome1 -proxywsdl=http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL -endpoint=http://webservices.daehosting.com/services/TemperatureConversions.wso -httpproxy=myproxy.com:80

Note I have specified my proxy again as the last parameter. If your environment does not use a proxy server leave this out.
[oracle@sracanov-au2 dbhome_1]$ cd sqlj
[oracle@sracanov-au2 sqlj]$ mkdir example2
[oracle@sracanov-au2 sqlj]$ cd example2/
[oracle@sracanov-au2 example2]$ jpub -u scott/tiger -sysuser sys/welcome1 -proxywsdl=http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL -endpoint=http://webservices.daehosting.com/services/TemperatureConversions.wso -httpproxy=myproxy.com:80
tmp/src/genproxy/TemperatureConversionsSoapClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Executing plsql_grant.sql
Loading plsql_proxy.jar

[oracle@sracanov-au2 example2]$
Note that when using jpub I can avoid coding PL/SQL code. The PL/SQL files can be found in the current local directory where jpub is run from. They have been executed while running the jpub command.
[oracle@sracanov-au2 example2]$ ls -al
total 88
drwxr-xr-x  3 oracle oinstall  4096 Dec 16 07:27 .
drwxr-xr-x  8 oracle oinstall  4096 Dec 15 10:43 ..
-rw-r--r--  1 oracle oinstall    95 Dec 16 07:27 plsql_dropper.sql
-rw-r--r--  1 oracle oinstall   548 Dec 16 07:27 plsql_grant.sql
-rw-r--r--  1 oracle oinstall 26546 Dec 16 07:27 plsql_proxy.jar
-rw-r--r--  1 oracle oinstall   404 Dec 16 07:27 plsql_revoke.sql
-rw-r--r--  1 oracle oinstall  3261 Dec 16 07:27 plsql_wrapper.sql
drwxr-xr-x  4 oracle oinstall  4096 Dec 16 06:43 tmp
b. Now call the web Service as in sqlplus.
SQL> conn scott/tiger
Connected.
SQL> select jpub_plsql_wrapper.celciusToFahrenheit(30) as "Celcius To Fahrenheit(30)" from dual;

Celcius To Fahrenheit(30)
-------------------------
                       86

SQL>
Running jpub has created the java webservice proxy for all the methods available from the webservice:
SQL> select jpub_plsql_wrapper.fahrenheitToCelcius(86) as "Fahrenheit To Celcius(86)" from dual;

Fahrenheit To Celcius(86)
-------------------------
                       30

SQL>
For any further or clarification, see the following documentation:

Oracle® Database JPublisher User's Guide
http://download.oracle.com/docs/cd/B28359_01/java.111/b31226/toc.htm

Database Web Service Callout Utility 10.1.3.1
http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm