PL/SQL Web Services
During the ODD for Web services today in Bellevue a student asked about how
the heck to publish a PL/SQL procedure, as simple as possible, as a Web service.
He will get to do it tomorrow in the course but in the meantime the quick and
dirty if you are JDeveloper user is this tutorial.
This tutorial by far the quickest but masks what is going on under the covers.
Further, sometimes however folks don't want to use JDeveloper but want to use
a command line tool. Here is a simple example to try out, assuming you have
OC4J 9.0.4 handy. The documentation showing all the knobs and dials behind this
is here:
http://download-west.oracle.com/docs/cd/B10464_01/web.904/b10447/plsqlservices.htm#i1028178
So here goes. First start with your favourite PL/SQL package. Here is a trivial
example to get the date from your server that can be installed into, for the
purposes of this example, into the SCOTT schema using SQLPlus.
CREATE OR REPLACE PACKAGE DBFUNC AS
FUNCTION GETDATE
RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY DBFUNC
AS
FUNCTION GETDATE
RETURN DATE IS
BEGIN
RETURN SYSDATE;
END GETDATE;
END DBFUNC;
/
Note PL/SQL Web services require the procedure/function to be within a PL/SQL
package.
Now for the steps to make that into a Web service:
1. First create a configuration file (just an ordinary text file) that the OC4J
Web Services Assembler file can use to generate the Web Services configuration
for that PL/SQL procedure.
Below is mine with some things specific to my machine - e.g. my machine name
mlehmann-lap and SID of O920. Save to this some file name, in this example I
used config.xml:
<web-service>
<display-name>PLSQL Web Service</display-name>
<description>PLSQL Sample</description>
<destination-path>./dbfunc.ear</destination-path>
<temporary-directory>/tmp</temporary-directory>
<context>/psqlsample</context>
<stateless-stored-procedure-java-service>
<jar-generation>
<schema>scott/tiger</schema>
<db-url>jdbc:oracle:thin:@mlehmann-lap:1521:o920<;/db-url>
<prefix>dbfunc.getdate</prefix>
<db-pkg-name>dbfunc</db-pkg-name>
</jar-generation>
<uri>/dbfunc</uri>
<database-JNDI-name>jdbc/OracleDS</database-JNDI-name>
</stateless-stored-procedure-java-service>
<wsdl-gen>
<wsdl-dir>wsdl</wsdl-dir>
<option name="force">true</option>
<option name="httpServerURL">http://mlehmann-lap:8888</option>
</wsdl-gen>
<proxy-gen>
<proxy-dir>proxy</proxy-dir>
<option name="include-source">true</option>
</proxy-gen>
</web-service>
Note, this sample configuration file assumes you
have a data source set up in your $ORACLE_HOME/j2ee/home/config data-sources.xml
named "jdbc/OracleDS" as follows (where $ORACLE_HOME is where your OC4J is unzipped
and the machine name mlehmann-lap and SID of O920 matches your configuration):
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="OracleDS"
location="jdbc/OracleCoreDS"
xa-location="jdbc/xa/OracleXADS"
ejb-location="jdbc/OracleDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="scott"
password="tiger"
url="jdbc:oracle:thin:@mlehmann-lap:1521:o920"
inactivity-timeout="30"
/>
2. Next run the Web Services Assembler tool to set up the configuration of
the PL/SQL Web service. This produces an ear file as per the configuration file
of dbfunc.ear
export ORACLE_HOME=/home/mlehmann/oc4j904
export CLASSPATH=
$ORACLE_HOME/webservices/lib/wsdl.jar:
$ORACLE_HOME/lib/xmlparserv2.jar:
$ORACLE_HOME/soap/lib/soap.jar
java -jar $ORACLE_HOME/webservices/lib/WebServicesAssembler.jar -config /home/mlehmann/temp/config.xml
Note on Linux that I exported the CLASSPATH; without the export the compile
that happens under the covers by the Web services assembler fails.
3. Once the ear file is done, it can be deployed to OC4J. Again following the
command line approach, simply run the OC4J deployment utility using admin.jar:
java -jar $ORACLE_HOME/j2ee/home/admin.jar ormi://mlehmann-lap admin welcome
-deploy -file ./dbfunc.ear -deploymentName dbfunc
java -jar $ORACLE_HOME/j2ee/home/admin.jar ormi://localhost admin welcome -bindWebApp
dbfunc dbfunc_web http-web-site /plsqlsample
4. Go to the Web service endpoint and test it out ... in my case that is
http://mlehmann-lap:8888/plsqlsample/dbfunc
I found that I needed to re-start OC4J after this deployment. This does not
seem right so am planning to check with the Web services db team if this is
a bug or to find out if I did something wrong.
8:09:36 AM 
|
