How to deploy a PL/SQL
procedure returning Ref Cursors as Web Service
Date: 23-Dec-2002
After completing this how-to you should be able to:
Use Web Services Assembler to bundle class files
for deployment to OC4J
Software Requirements
Notation
- <SAMPLE_HOME>- The directory
where the RefCursor.jar file was unzipped to. This directory
contains the RefCursor directory.
- <OC4J_HOME> - The directory where OC4J
is installed. For example, if oc4j_extended.zip was unzipped
to the C:\OC4J , then <OC4J_HOME> would be
C:\OC4J.
Introduction
This document demonstrates how to publish a PL/SQL
procedure that returns a REF CURSOR as a Web Service. Ref Cursor
is a commonly used PL/SQL data type which reduces the network
traffic. Ref Cursors are mapped to java.sql.ResultSet. But
ResultSet is not currently a supported type by the Web Service
framework. So a mapping of ResultSet to an accepted Java type
is required. This mapping can be done by using JPublisher subclassing
by mapping Ref Cursors (ResultSet) to String array. JPublisher
utility is shipped along with OC4J
This Web Services makes use of the EMPLOYEES table in
the HR/HR user of the sample schema provided with Oracle9i
database. A PL/SQL procedure that takes the name(first name
and last name) of a employee and return the names(first name
and last name) of his subordinates as a Ref Cursor is published
as a Web Service. For doing this JPublisher and Web Services
Assembler are used.
Extracting the source code
The application files are stored in an archive named
Refcursor.jar. Download the archive and extract it to a
convenient directory (<SAMPLE_HOME>). Execute following
command to extract the files:
jar xvf Refcursor.jar
The archive RefCursor.jar extracts the files that
implement the Sample RefCursor directory. The zip file contains
the following files and directories:
Steps for deploying the PL/SQL procedure as Web Service
The following are the sequence of steps required to
publish the PL/SQL procedure as a Web Service.
1.
|
Create the PL/SQL procedure by executing
the file CreateProcedure.sql located in the directory
<SAMPLE_HOME>/RefCursor/config.
To execute the script CreateProcedure.sql,
connect to your database as hr/hr and run the following
command from the SQL prompt
SQL>@<SAMPLE_HOME>/RefCursor/config/CreateProcedure.sql
This creates a new package EmpData with
a function getSubordinates function.
|
2.
|
Make sure you have the following in
your CLASSPATH that is required by JPublisher and for
compiling SQLJ files
-
SQLJ translator classes (translator.jar present at <OC4J_HOME>\sqlj\lib)
-
SQLJ runtime classes (runtime12ee.jar present at <OC4J_HOME>\sqlj\lib)
-
JDBC classes (classes12dms.jar <OC4J_HOME>\jdbc\lib)
|
3.
|
The next step is to use JPublisher to
generate the java (SQLJ) file for the above create package.
Make sure jpub is present in the PATH.
jpub is present in the <OC4J_HOME>\bin
directory. From any convenient directory give the following
command to generate the java files.
>jpub -sql=EMPDATA:EmpBase:EmpImp
-package=EmpData -user=hr/hr
-url=jdbc:oracle:thin:@<hostname>:<port>:<sid>
where,
|
EMPDATA |
The name
of the package in the database |
|
EmpBase |
The base
class where JPub generates its code |
EmpImp
|
The initial
version of EmpImp.sqlj where user specific code
goes |
<hostname>
|
Host name
where the database is installed
|
<port>
|
TNS listener
port of the database
|
<SID>
|
Database
name
|
This creates EmpBase.sqlj and EmpImp.sqlj
in the same directory. JPublisher places its code into
EmpBase.sqlj and also creates an initial version of
EmpImp.sqlj, where you can provide your own code to
override existing methods in EmpBase.sqlj.
|
4.
|
You have a PL/SQL method that returns
a REF CURSOR. JPublisher will automatically map the
return type to java.sql.ResultSet. You will have the
following method in the EmpBase class:
public java.sql.ResultSet getsubordinates (String firstname,String lastname)
ResultSet type is not an acceptable
Web Service parameter. You need to create a function
that maps ResultSet to a type acceptable as Web Services
parameter. You can map ResultSet to String[ ]. To do
this you need to place a new method EmpImp.sqlj that
can return the ResultSet as String[ ], such as :
public String[] getSubordinatesNames(String firstName, String lastName)
Now you can add the mapping method in
EmpImp.sqlj. Also make sure to include the constructors
that call the respective super class constructors.
Finally the class should look like EmpImp.sqlj
provided along with the sample jar.
|
| 5.
|
Next you need to ensure that the getsubordinates(String
name) method is omitted from the operations provided
by your Web Service, and that the
getSubordinatesNames(String ename) method is included
instead. To accomplish this define an interface, say
EmpInter, that contains exactly those methods that you
want to publish.
Your interface should look like one in EmpInter.java
provided along with the sample.
|
6.
|
Navigate to the directory where sqlj
and java files are created. Compile the SQLJ file and
the java file to create the class files. Make sure
sqlj.exe (at <OC4J_HOME>\bin directory) is present
in the PATH
>javac -d . *.java
>sqlj -d . *.sqlj
This will create a directory EmpData
and put all the class files into it.
|
7.
|
Create a JAR, say EmpData.jar, of the
EmpData directory.
>jar cvfM EmpData.jar EmpData
|
8
|
Now you can use the Web Services Assembler
to create a deployable Web Service Enterprise
Application aRchive (EAR) file.
The WebServicesAssembler.jar is present at
<OC4J_HOME>\webservices\lib
You need to provide inputs to the
WebServicesAssembler.jar through a configuration file
config.xml. A sample config.xml is present in the
<SAMPLE_HOME>\RefCursor\config.xml. You can copy
this config.xml and EmpData.jar created in the previous
step to <OC4J_HOME>\webservices\lib.
The config.xml contains information
about the jar to deploy, the class that implements
the service, the interface file, data source etc.
The data source is provided as shown below.
<!-- Info
needed at runtime - the JNDI DB connection -->
<database-JNDI-name>jdbc/ora9idb</database-JNDI-name>
Make sure to change the data source
name to the one that connects to the EMPDATA package
in the HR user of sample schema. If the data source
does not exist, you can provide a new data source name
by creating the following entry in the
<OC4J_HOME>\j2ee\home\config\data-sources.xml.
<data-source
class="oracle.jdbc.pool.OracleDataSource"
name="ora9idb"
location="jdbc/ora9idb"
xa-location="jdbc/xa/ora9idbXADS"
ejb-location="jdbc/ora9idb"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="hr"
password="hr"
url="jdbc:oracle:thin:@<hostname>:<port>:<sid>"
inactivity-timeout="30"
/>
Note: Add valid values for hostname,
port and SID
Now give the following command to generate
the EAR file from <OC4J_HOME>\webservices\lib
>java -jar WebServicesAssembler.jar -config config.xml
This will create the EmpData.ear file.
|
9.
|
Copy the above created ear file
to: <OC4J_HOME>\j2ee\home\applications directory.
|
10.
|
Start the OC4J server. For this, go
to <OC4J_HOME>\j2ee\home folder and type the
following
>java -jar oc4j.jar
|
11.
|
Deploy the EAR from <OC4J_HOME>\j2ee\home
using
>java -jar admin.jar
ormi://<server>:<rmi-port> admin
<password> -deploy -file <OC4J_HOME>\webservices\lib\EmpData.ear
-deploymentName EmpData
This will deploy the application with
name "EmpData"
Now, bind the web application to the
web site using the following command,
>java -jar admin.jar
ormi://<server>:<rmi-port> admin
<password> -bindWebApp EmpData EmpData_web
http-web-site /PLSQL
where,
| http-web-site
|
the website
to bind the web application |
| server
|
Host/ IP of this site
|
| rmi-port
|
Port of this site
|
| password
|
admin password
for the OC4J server |
|
12.
|
You can see the working of the Web Service
by opening a browser and going to the following URL
http://<server>:<http-port>/PLSQL/EmpData
You will see a list of the operations
of your Web Service and be able to test operations
individually through your browser.
The endpoint of the Web Service is
http://<server>:<http-port>/PLSQL/EmpData?WSDL
where,
| server
|
Host/ IP of this site
|
| http-port
|
Http port of this site
|
|
Summary
After going through this document you should have
understood how to deploy a PLSQL procedure which return ref
cursor as a Web Service using JPublisher and Web Services Assembler
References
1. For information on how to use JDeveloper to deploy
a PLSQL procedure as a Web Service see
/tech/webservices/htdocs/series/plsql/index.html
2. For information on Oracle9i database and
Web Services see /tech/webservices/database.html
|
Bookmark
