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

Go to the "sql" subdirectory and run the script create.sql as SCOTT against the database.
SQL> conn scott/tiger
SQL> @create.sql

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. Press the ENTER key to proceed after SQL scripts are executed. If the database used is remote, ignore the "Oracle no available" errors, instead run the suggested SQL script against the database used and then press the ENTER key. The last target "run-client" should give the following result:
[java] Successfully added an employee
[java] TIMESTAMP test done: false
[java] BOOLEAN Test returned true
[java] SW 5Th Ave. Portland OR 97204
[java] Tony Blick 1181000.0
[java] SW 5Th Ave. Portland OR 97204
[java] Holder Test - Get Emp ID: 1000.0, Name: Tony.Blick
[java] Successfully removed an employee
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. One 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 a PL/SQL package into a Webservices application.  The following files will be generated.

Step 5a(2) Run the PL/SQL Wrapper

When the PL/SQL stored procedure being published in Step 5a(1) contains a PL/SQL-specific type such as PL/SQL RECORD, the assembler will create the PL/SQL wrapper, Company_plsql_wrapper.sql, to define the SQL type corresponding to that PL/SQL type and also define the conversion functions between the SQL and PL/SQL types. After assembly one needs to install that PL/SQL wrapper into the SCOTT schema.
SQL>conn scott/tiger
SQL>@Company_plsql_wrapper.sql

Step 5a(3) Deploy

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

Step 5a(4) Bind

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

Step 5a(5) 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(6) Run the Client

The client program,
src/client/oracle/demo/db/plsql/CompanyClient.java
, uses the client proxy, generated in Step 5a(5), 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/plsql/plsql

Step 7. Clean the Database

To clean the database after the demo, run the dropping script in the current directory.
SQL> conn scott/tiger
SQL> @Company_plsql_dropper.sql
Run the drop.sql script in the sql subdirectory to clean the schema created by the script create.sql.
SQL> conn scott/tiger
SQL> @drop.sql