How to deploy a PL/SQL function returning
BLOB as Web Service
Date: 7-Jan-2003
After completing this how-to you should be able to:
Software Requirements
Notation
- <SAMPLE_HOME>- The directory
where the Blob.jar file was unzipped to. This directory
contains the Blob 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.
- <ORACLE_HOME> - The directory where
the Oracle9i Client or Database software is installed
Introduction
This document demonstrates how to publish a PL/SQL
function that returns a BLOB as a Web Service. BLOB is a
commonly used column type for storing large content like
images in the database. PL/SQL and JDBC support BLOB. But
BLOB is currently not a supported type by the Web Service
framework. So a mapping of BLOB to an accepted Java type
is required. This mapping can be done by using JPublisher
subclassing by mapping BLOB to byte array. JPublisher utility
is shipped along with OC4J.
This Web Service makes use of the PRINT_MEDIA table
in the PM/PM user of the sample schema provided with Oracle9i
database. This table contains information used for advertisement
of products. This table has a column AD_PHOTO which contains
the image used in the advertisement of the product as a BLOB
column. A PL/SQL function that takes the product ID and
return this image as a BLOB is published as a Web Service.
This is achieved using JPublisher and WebServices Assembler.
Note: BLOB will be supported type by the web services
framework in OC4J 9.0.4
Extracting the source code
The application files are stored in an archive named
Blob.jar. Download the archive and extract it to a convenient
directory (<SAMPLE_HOME>). Execute following command
to extract the files:
>jar xvf Blob.jar
This creates the 'Blob' directory and extracts the
following files and directories :
- src/ -
contains all Java source code for the example.
-
- BlobBase.sqlj
- SQLJ file generated by JPublisher
- BlobImp.sqlj
- A Sample Implementation file of the required
methods.
- BlobInter.java -
A Sample Implementation of the interface
- config/- contains
configuration files.
-
- CreateFunction.sql
- The sql script used to create the PL/SQL function
- config.xml
-XML file containing the inputs for Web
Services Assembler
Steps for deploying the PL/SQL procedure as Web
Service
The following are the sequence of steps required
to publish the PL/SQL function as a Web Service.
|
1.
|
Create the PL/SQL function by executing
the file CreateFunction.sql located in the directory
<SAMPLE_HOME>/Blob/config.
To execute the script CreateFunction.sql,
connect to your database as pm/pm and run the following
command from the SQL prompt
SQL>@<SAMPLE_HOME>/Blob/config/CreateFunction.sql
This creates a new package AD with
a function getAdPhoto function.
Note: If the user account is locked,
then connect to sytem/manager and execute the folloing
command
SQL>ALTER USER pm IDENTIFIED BY pm ACCOUNT UNLOCK
|
|
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 (classes12.jar <ORACLE_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=AD:BlobBase:BlobImp
-package=ad -user=pm/pm
-url=jdbc:oracle:thin:@<hostname>:<port>:<sid>
where,
|
AD |
The name
of the package in the database |
|
BlobBase |
The base
class where JPub generates its code |
BlobImp
|
The initial
version of sub class where user code goes
|
<hostname>
|
Host
name where the database is installed
|
<port>
|
TNS listener
port of the database
|
<SID>
|
Database
name
|
This creates BlobBase.sqlj and BlobImp.sqlj
in the same directory. JPublisher places its code
into BlobBase.sqlj and also creates an initial version
of BlobImp.sqlj, where you can provide your own code
to override existing methods in BlobBase.sqlj.
|
|
4.
|
You used a PL/SQL function that returns
a BLOB. JPublisher will automatically map the return
type to oracle.sql.BLOB. You will have the following
method in the BlobBase class:
public oracle.sql.BLOB getadphoto(Integer productid) throws SQLException
BLOB type is not an acceptable Web
Service parameter. You need to create a function that
maps BLOB to a type acceptable as Web Services parameter.
You can map BLOB to byte[ ]. To do this you need
to place a new method BlobImp.sqlj that can return
the BLOB as byte[ ], such as :
public byte[] getAdPhotoAsByte(Integer productId) throws SQLException
Now you can add this mapping method
in BlobImp.sqlj. Also make sure to include the constructors
that call the respective super class constructors.
Finally the class should look like
BlobImp.sqlj provided along with the sample jar.
|
|
5.
|
Next you need to ensure that the getadphoto(Integer
productid) method is omitted from the operations provided
by your Web Service, and that the
getAdPhotoAsByte(Integer productId) method is included
instead. To accomplish this define an interface, say
BlobInter, that contains exactly those methods that you
want to publish.
Your interface should look like one in BlobInter.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 (at <OC4J_HOME>\bin directory) is present
in the PATH
>javac -d . *.java
>sqlj -d . *.sqlj
This will create a directory 'ad' and
put all the class files into it.
|
|
7.
|
Create a JAR, say ad.jar, of the ad directory.
>jar cvfM ad.jar ad
|
|
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>\Blob\config.xml. You can copy this
config.xml and ad.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/pm_ora9idb</database-JNDI-name>
Make sure to change the data source
name to the one that connects to the AD package in
the PM 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="pm_ora9idb"
location="jdbc/pm_ora9idb"
xa-location="jdbc/xa/pm_ora9idbXADS"
ejb-location="jdbc/pm_ora9idb"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="pm"
password="pm"
url="jdbc:oracle:thin:@<hostname>:<port>:<sid>"
inactivity-timeout="30"
/>
Note: Add valid values for hostname,
port and SID
|
|
9.
|
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 ad.ear file.
|
|
10.
|
Copy the above created ear file
to: <OC4J_HOME>\j2ee\home\applications directory.
|
|
11.
|
Start the OC4J server. For this, go
to <OC4J_HOME>\j2ee\home folder and type the
following
>java -jar oc4j.jar
|
|
12.
|
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\ad.ear
-deploymentName ad
This will deploy the application with
name "ad"
Now, bind the web application to the
web site using the following command,
>java -jar admin.jar
ormi://<server>:<rmi-port> admin
<password> -bindWebApp ad ad_web http-web-site
/BLOB
where,
| http-web-site
|
the website
to bind the web application |
| server
|
Host/ IP of this site
|
| rmi-port
|
RMI port of this site
|
| password
|
admin password
for the OC4J server |
|
|
13.
|
You can see the working of the Web
Service by opening a browser and going to the following
URL
http://<server>:<http-port>/BLOB/ad
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>/BLOB/ad?WSDL
where,
| server
|
Host/ IP of this site
|
| http-port
|
Http port of this
site |
The web service client will get the
image as byte[]. A GIF file could be created out
of this and viewed through a image viewer.
|
Summary
After going through this document you should have
understood how to deploy a PLSQL function which return BLOB
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
