Steve's Blog Technical Information on Oracle Middleware.

Im my preivous blog I created a simple webservice and used plsql to call the webservice from the database. Here I will use jpub to generate my java proxy and use that to call my webservice without the need to code any plsql. Note all of these steps are done upon the previous blog beginning completed.

1. Download and unzip jpub. You can get it from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Im using JPublisher 10g Release 10.2

C:\Documents and Settings\stever\Desktop> set_database.bat
C:\Documents and Settings\stever\Desktop> oracle_home
D:\Oracle\product\10.2.0\db_1
C:\Documents and Settings\stever\Desktop> unzip jpub_102.zip -d %oracle_home%
Archive:  jpub_102.zip
  inflating: D:/Oracle/product/10.2.0/db_1/sqlj/lib/sqljutl.sql
  inflating: D:/Oracle/product/10.2.0/db_1/sqlj/lib/translator.jar
...

2. Set the classpath and run jpub.

C:\Documents and Settings\stever\Desktop>cdo

D:\Oracle\product\10.2.0\db_1

D:\Oracle\product\10.2.0\db_1>cd sqlj\bin

D:\Oracle\product\10.2.0\db_1\sqlj\bin>ll
 Volume in drive D is ACERDATA
 Volume Serial Number is 4059-B823

 Directory of D:\Oracle\product\10.2.0\db_1\sqlj\bin

07/21/2005  03:34 AM    <DIR>          .
07/21/2005  03:34 AM    <DIR>          ..
07/21/2005  03:34 AM             1,376 jpub
07/21/2005  03:34 AM             2,834 jpub.c
07/21/2005  03:34 AM            50,619 jpub.exe
07/21/2005  03:34 AM               267 README.txt
               4 File(s)         55,096 bytes
               2 Dir(s)   8,243,344,384 bytes free

D:\Oracle\product\10.2.0\db_1\sqlj\bin> cd ..
D:\Oracle\product\10.2.0\db_1\sqlj>java_home
D:\Oracle\product\10.2.0\db_1\jdk

D:\Oracle\product\10.2.0\db_1\sqlj>classpath
.;D:\Oracle\product\10.2.0\db_1\sqlj\lib\translator.jar;
D:\Oracle\product\10.2.0\db_1\sqlj\lib\runtime12.jar;
D:\Oracle\product\10.2.0\db_1\sqlj\lib\dbwsa.jar;D:\Oracle\product\10.2
.0\db_1\javavm\lib\aurora.zip;
D:\Oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar;

D:\Oracle\product\10.2.0\db_1\sqlj>jpub -u wsuser/wsuser -sysuser sys/welcome1
-proxywsdl=http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort?WSDL
-endpoint=http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort
tmp\src\genproxy\MyWebService1SoapHttpPortClientJPub.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

3. Call the webservice.

D:\Oracle\product\10.2.0\db_1\sqlj>sqlplus wsuser/wsuser

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 10 19:36:58 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select jpub_plsql_wrapper.sayhelloworld from dual;

SAYHELLOWORLD
--------------------------------------------------------------------------------
Hello World

SQL> select jpub_plsql_wrapper.sayhelloname('Steve') from dual;

JPUB_PLSQL_WRAPPER.SAYHELLONAME('STEVE')
--------------------------------------------------------------------------------
Hello Steve

SQL>

NOTE: My set_database.bat file simply has my environment settings in there. 
This is where I set the classpath. It looks like:

set ORACLE_HOME=D:\Oracle\product\10.2.0\db_1
set ORACLE_SID=ORCL
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%ORACLE_HOME%\bin;%TT_HOME%\bin;%JAVA_HOME%\bin;%ORACLE_HOME%\sqlj\bin;%PATH%
set TNS_ADMIN=D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN
set 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\ojdbc14.jar;