Creating Database Web Services for Invoking PL/SQL Stored Procedures

Contents

Overview

This example shows how to publish a PL/SQL stored procedure package into a Web Service. The PL/SQL package, Company, defined by sql/create.sql, contains a number of methods. Each method will be exposed as a Web Service operation.

To run this demo you need to ensure that you have installed the required software and set up your environment. In addition, an installation of the Oracle database is required.

Step By Step

Step 1. Prepare the Database

The demo requires the present of the EMP table in the database. The table is created during installtion for the SCOTT schema in Oracle databases. To create the EMP table in a database, run the script ${ORACLE_HOME}/bin/demobld in SQLPLUS.

Step 2. Prepare the OC4J Instance

If the database is not running locally, modify the
J2EE_HOME/data-sources.xml file so that the data-source
"jdbc/OracleDS" points to the running database, for instance,
<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:@<host>:<port>:<sid>"
inactivity-timeout="30"
/>

Step 3. Start OC4J

Go to the J2EE_HOME directory and start the OC4J instance standalone.
java -jar oc4j.jar

Step 4. Prepare service-config.xml

If the database is not running locally, modify the file
service-config.xml, so that the <db-conn/> element points
to the running database, in the format of
jdbc:oracle:thin:@<host>:<port>:<sid>

Step 5. Run the Demo with ANT

Run the command
ant
in the current directory. The last target "run-client" should give the following result:
[java] *** Query Emp Rows by ID returns 1 rows
[java] *** Query Emp Rows by ID returns <7900,JAMES>
This step accomplish several tasks including assembling, deploying, binding the application and then running the client. In the next section, Step 5a (Alternative to Step 5), we will detail how this step is implemented with separate commands.

Step 5a (Alternative to Step 5). Run the Demo with Separate Commands

The section provides a detaiedl view of the previous section. The user should run either Step 5 or Step 5a but not both.

Step 5a(1) Assembly

Run the WebServices assembler as following.
java -jar $ORACLE_HOME/webservices/wsa.jar -config service-config.xml
This step publishes queries defined in service-config.xml into Webservices.  The following files will be generated.

Step 5a(2) Deploy

Deploy the generated WebServices application.
java -jar $J2EE_HOME/admin.jar ormi://localhost admin welcome -deploy -file dist/query.ear -deploymentName query

Step 5a(3) Bind

Bind the application to http-web-site.
java -jar $J2EE_HOME/admin.jar ormi://localhost admin welcome -bindWebApp query query-web http-web-site /query

Step 5a(4) Generate the Client Proxy

Run the WebServices assembler to generate the client proxy.
java -jar $ORACLE_HOME/webservices/wsa.jar -config client-config.xml
The client proxy will be generated in the subdirectory build/classes/client.

Step 5a(5) Run the Client

The client program,
src/client/oracle/demo/db/query/QueryClient.java
, uses the client proxy, generated in Step 5a(4), to invoke the WebServices operations.

Compile and run the client program using the ANT commands:

ant compile-client
ant run-client
The target "run-client" will give the result described in Section 5.

Step 6. Access the Services via Web Browser

The services can also be accessed via the URL
http://localhost:8888/query/query