Using jpub to create webserivce proxy for callouts.

Posted by Steve Racanovic | Posted in , | Posted on 11:58 AM

2

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;

Using UTL_DBWS Package To Call A Web Service From PL/SQL Code.

Posted by Steve Racanovic | Posted in | Posted on 3:12 PM

2

I am going to show a simple example that will call my web service from my database. I will be using JDev 10.1.3.x to create and run my web service. My database and web service will all be running from my local machine.

1. My first step is to upgrade my database to 10.2.0.2 or later. UTL_DBWS will not work on 10.2.0.1. While its upgrading, I need to download the latest UTL_DBWS from
http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html

* I am going to use 10.1.3.1 Callout Utility for 10g and 11g RDBMS (ZIP, ~13MB)

Extract the downloaded file like:

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME
unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj
unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

2. Ensure that I have $ORACLE_HOME/bin in my path. First, I need to run the following command to load the libraries (run this from the $ORACLE_HOME/sqlj/lib folder).

loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb102.jar

The username and password is this example will be the sys user.


Check my database is upgraded/correct and load the jar files.

NOTE: This may take a while to run.

3. Create a new user to use web services utl_dbws package and give grant privileges.

create user wsuser identified by wsuser
default tablespace users
temporary tablespace temp
/

grant connect, resource to wsuser;



4. Once the packages had been loaded (step 2), the next step is to provide grants from the schema where the jar files have been loaded to our 'WSUSER'. These grants will have to run from where the libraries are installed. 'SYS' user.


execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission', 

'accessClassInPackage.sun.util.calendar','');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'getClassLoader','');



execute dbms_java.grant_permission('<user schema>','SYS:java.net.SocketPermission','*',

'connect,resolve');



execute dbms_java.grant_permission('<user schema>','SYS:java.util.PropertyPermission','*',

'read,write');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'setFactory','');






5. Now using Jdev, I create my java class as follows.


package ws;

public class HelloWorld {
public HelloWorld() {
}

public String sayHelloWorld(){
return "Hello World";
}

public String sayHelloName(String n){
return "Hello " + n;
}
}


6. I then create a web service from this class.



* Select "J2EE 1.4 (JAX-RPC) Web Service"

I then click on finished at Step 1 of 8 of the 'Create Java J2EE 1.4 Web Service' wizard so it uses all the default setting.

7. Then I ran the web service in the embedded server.



And checked my WS WSDL to ensure it is running.

http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort
http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort?WSDL



This is how my deployed WSDL looks like:

This XML file does not appear to have any style information associated with it. The document tree is shown below.


-<definitions name="MyWebService1" targetNamespace="http://ws/">
-<types>
-<schema targetNamespace="http://ws/types/" elementFormDefault="qualified">
-<element name="sayHelloNameElement">
-<complexType>
-<sequence>
<element name="n" type="string" nillable="true"/>
-</sequence>
</complexType>
</element>
-<element name="sayHelloNameResponseElement">
-<complexType>
-<sequence>
<element name="result" type="string" nillable="true"/>
</sequence>
</complexType>
</element>
-<element name="sayHelloWorldElement">
-<complexType>
<sequence/>
</complexType>
</element>
-<element name="sayHelloWorldResponseElement">
-<complexType>
-<sequence>
<element name="result" type="string" nillable="true"/>
</sequence>
</complexType>
</element>
</schema>
</types>
-<message name="MyWebService1_sayHelloName">
<part name="parameters" element="tns0:sayHelloNameElement"/>
</message>
-<message name="MyWebService1_sayHelloNameResponse">
<part name="parameters" element="tns0:sayHelloNameResponseElement"/>
</message>
-<message name="MyWebService1_sayHelloWorld">
<part name="parameters" element="tns0:sayHelloWorldElement"/>
</message>
-<message name="MyWebService1_sayHelloWorldResponse">
<part name="parameters" element="tns0:sayHelloWorldResponseElement"/>
</message>
-<portType name="MyWebService1">
-<operation name="sayHelloName">
<input message="tns:MyWebService1_sayHelloName"/>
<output message="tns:MyWebService1_sayHelloNameResponse"/>
</operation>
<operation name="sayHelloWorld">
<input message="tns:MyWebService1_sayHelloWorld"/>
<output message="tns:MyWebService1_sayHelloWorldResponse"/>
</operation>
</portType>
-<binding name="MyWebService1SoapHttp" type="tns:MyWebService1">
<soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
-<operation name="sayHelloName">
<soap:operation soapAction="http://ws//sayHelloName"/>
-<input>
<soap:body use="literal"/>
</input>
-<output>
<soap:body use="literal"/>
</output>
</operation>
-<operation name="sayHelloWorld">
<soap:operation soapAction="http://ws//sayHelloWorld"/>
-<input>
<soap:body use="literal"/>
</input>
-<output>
<soap:body use="literal"/>
</output>
</operation>
</binding>
-<service name="MyWebService1">
-<port name="MyWebService1SoapHttpPort" binding="tns:MyWebService1SoapHttp">
<soap:address
location="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort"/>
</port>
</service>
</definitions>


8. Now using the UTL_DBWS package, I will create my PL/SQL code.


CREATE OR REPLACE PACKAGE HelloWorld
AS

FUNCTION SayHelloWorld RETURN VARCHAR2;
FUNCTION sayHelloName(yourname IN VARCHAR2) RETURN VARCHAR2;

END HelloWorld;
/

CREATE OR REPLACE PACKAGE BODY HelloWorld AS

FUNCTION SayHelloWorld
RETURN VARCHAR2
IS

service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
xoperation_qname sys.utl_dbws.QNAME;
xstring_type_qname sys.utl_dbws.QNAME;
response sys.XMLTYPE;
request sys.XMLTYPE;

BEGIN
service_qname := sys.utl_dbws.to_qname(null, 'SayHelloWorld');
service_ := sys.utl_dbws.create_service(service_qname);
call_ := sys.utl_dbws.create_call(service_);
sys.utl_dbws.set_target_endpoint_address(call_
, 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

sys.utl_dbws.set_property( call_
, 'SOAPACTION_USE'
, 'TRUE'
);

sys.utl_dbws.set_property( call_
, 'SOAPACTION_URI'
, 'http://ws//sayHelloWorld'
);

sys.utl_dbws.set_property( call_
, 'OPERATION_STYLE'
, 'document'
);

request := sys.XMLTYPE('<sayHelloWorldElement xmlns="http://ws/types/" />');

response :=sys. utl_dbws.invoke(call_, request);
return response.extract('//child::text()'
, 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
MyWebService1SoapHttpPort"'
).getstringval();

END SayHelloWorld;

FUNCTION sayHelloName(yourname IN VARCHAR2) RETURN VARCHAR2 AS

service_ sys.utl_dbws.SERVICE;
call_ sys.utl_dbws.CALL;
service_qname sys.utl_dbws.QNAME;
port_qname sys.utl_dbws.QNAME;
xoperation_qname sys.utl_dbws.QNAME;
xstring_type_qname sys.utl_dbws.QNAME;
response sys.XMLTYPE;
request sys.XMLTYPE;

BEGIN
service_qname := sys.utl_dbws.to_qname(null, 'SayHelloName');
service_ := sys.utl_dbws.create_service(service_qname);
call_ := sys.utl_dbws.create_call(service_);
sys.utl_dbws.set_target_endpoint_address(call_
, 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

sys.utl_dbws.set_property( call_
, 'SOAPACTION_USE'
, 'TRUE'
);

sys.utl_dbws.set_property( call_
, 'SOAPACTION_URI'
, 'http://ws//sayHelloName'
);

sys.utl_dbws.set_property( call_
, 'OPERATION_STYLE'
, 'document'
);

request := sys.XMLTYPE('<sayHelloNameElement xmlns="http://ws/types/">'
||'<n>' || yourname || '</n> </sayHelloNameElement>');

response :=sys. utl_dbws.invoke(call_, request);
return response.extract('//child::text()'
, 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
MyWebService1SoapHttpPort"'
).getstringval();

END SayHelloName;

END HelloWorld;
/


9. I then ran my package:

select helloworld.sayhelloworld from dual;

select helloworld.sayhelloname('Steve') from dual;

Performance using Bind Variables in Java Application

Posted by Steve Racanovic | Posted in , | Posted on 1:03 PM

1

The advantage of using bind variables within your Java application, it that provides greater application performance and uses less of the shared pool from the database.

This is illustrated in the following 2 examples.

Example 1: Using Bind variables.


package bindvariables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import bindvariables.JDBCUtil;

public class Bind {

public static void main(String[] args) throws SQLException {

ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;

try {
// Get Connection
conn = JDBCUtil.getConnection();
// Create statement
pstmt = conn.prepareStatement("SELECT ? FROM dual");
System.out.println("Start: " + new Date());
for (int i = 1; i < 100000; i++) {
// Use bind variable for subsitution
pstmt.setString(1, String.valueOf(i));
// Execute the query
rs = pstmt.executeQuery();
//if (i % 1000 == 1)
// System.out.println("Print: " + i);
}
System.out.println("End: " + new Date());

} catch (SQLException se) {
System.out.println("SQL Exception:");
se.printStackTrace();
}
finally {
// Close the result set, statement and the connection
JDBCUtil.close(rs,pstmt,conn);
}
}
}


Running this application returns the following:

Start: Mon Mar 31 11:36:04 EST 2008
End: Mon Mar 31 11:36:51 EST 2008

It shows the application being executed within less than a minute.

Now the second example does not use bind variables and force the use of Hard Parse on the database.

Example 2: Not using bind variables.


package bindvariables;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class NonBind {

public static void main(String[] args) throws SQLException {

ResultSet rs = null;
Connection conn = null;
Statement stmt = null;

try {
// Get Connection
stmt = (JDBCUtil.getConnection()).createStatement();
System.out.println("Start: " + new Date());
for (int i = 0; i < 100000; i++) {
// Execute Statement
rs = stmt.executeQuery("SELECT " + i + " FROM dual");
//if (i % 1000 == 1)
// System.out.println("Print: " + i);
}
System.out.println("End: " + new Date());
} catch (SQLException se) {
System.out.println("SQL Exception:");
se.printStackTrace();
}
finally {
// Close the result set, pstatement and the connection
JDBCUtil.close(rs,stmt,conn);
}
}
}


Running this application returns the following:

Start: Mon Mar 31 11:31:45 EST 2008
End: Mon Mar 31 11:35:34 EST 2008

It shows the application being executed over 4 minutes.

As you can see, ensure you use bind variables for performance when coding your application.

My JDBCUtil class method look like:

...

public static void close(ResultSet resultSet, Statement statement,
Connection connection) {
try {
if (resultSet != null)
close(resultSet);
if (statement != null)
close(statement);
if (connection != null)
close(connection);
} catch (Exception e) {
e.printStackTrace();
}
}

public static void close(ResultSet resultSet, PreparedStatement pstatement,
Connection connection) {
try {
if (resultSet != null)
close(resultSet);
if (pstatement != null)
close(pstatement);
if (connection != null)
close(connection);
} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() throws SQLException {
String username = "scott";
String password = "tiger";
String thinConn =
"jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(thinConn, username, password);
conn.setAutoCommit(false);
return conn;
}
}


...

Database (Web Services Call-Out) class error

Posted by Steve Racanovic | Posted in , | Posted on 11:28 AM

2

When receiving the following error:

ERROR at line 1:
ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist

ORA-06512: at "SYS.UTL_DBWS", line 195

ORA-06512: at "INTERFACE.GET_JOKE", line 13

The java classes are not installed in the database user schema.

Logout of sqlplus and run:
loadjava -u / -r -v -f -s -grant public -genmissing  dbwsclientws.jar dbwsclientdb102.jar

Where user/password are the details of the database user using the database web service callout.

Further details can be found here:
http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm
http://www.oracle.com/technology/tech/java/oc4j/1003/how_to/how-to-ws-db-javacallout.html