Calling External Web Services from the Database (Web Services Call-Out)

Contents

Overview

This example shows how to call Web Services from the database. The demo performs the following basic steps.
  1. Assemble and deploy an EJB Web Service (this is the service we will invoke in this example).
  2. The JPublisher tool creates a client proxy to the published Web Service. The client proxy consists of a Java client proxy, which is to be loaded into the database, and a PL/SQL wrapper around that Java client proxy. JPublisher can automatically load the Java client proxy and PL/SQL wrapper into the database.
  3. Invoke the client proxy via the PL/SQL wrapper. The client proxy in turn invokes the EJB WebService.
To run this demo you need an installation of the Oracle database -either Oracle 9i or 10g running locally or remotely- as well as an OC4J installation to provide an external Web Service (see here for more installation instructions).

Step by Step

Step 1. Prepare the Database

If you are running an Oracle database prior to 10i production release, you need to load ${ORACLE_HOME}/sqlj/lib/sqljutl.jar and ult_dbws_jserver.jar into the database. One way to determine whether sqljutl.jar has been loaded is to describe the procedure "utl_dbws_validate" under SYS, which should have the following response:
SQL> conn / as sysdba
Connected.
SQL> describe utl_dbws_validate;
PROCEDURE utl_dbws_validate
To load the jar file into the server, perform the following steps.
  1. Make sure the database has java_pool_size and shared_pool_size equal or greater than 96M and 80M respectively. If not, modify the database pfile, e.g., init.ora, for the two entries, and restart the database.
  2. Go to the directory ORACLE_HOME
  3. Run the script sql/initdbws.sql or sql/initdbws9.sql under SYS:
    1. For 9i databases, run initdbws9.sql

      2. SQL>conn / as sysdba
      SQL>@initdbws9
    2. For 10i Beta databases, run initdbws.sql

      3. SQL>conn / as sysdba
      SQL>@initdbws
  4. Run the script [ORACLE_HOME]/sqlj/lib/sqljutl.sql under SYS, i.e.,

    5. SQL> conn / as sysdba
    SQL> @sqljutl.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. Deploy an EJB WebServices Application

Deploy and bind the EJB WebServices application, javacallout.ear.
java -jar ${J2EE_HOME}/admin.jar ormi://localhost admin welcome -deploy -file ./dist/javacallout.ear -deploymentName javacallout

java -jar ${J2EE_HOME}/admin.jar ormi://localhost admin welcome -bindWebApp javacallout javacallout http-web-site /javacallout

To verify that the deployment is sucessful, use the URL

http://localhost:8888/javacallout/javacallout?operation=sayHello

to invoke the sayHello method. The source code for the EJB application is located under the src subdirectory.

Step 5. Generate and Load Client Proxy and Wrapper

Generate the database client proxy using the the WSDL file for the webservice deployed.
jar xvf dist/javacallout.jar META-INF/HelloServiceEJB.wsdl

jpub -proxywsdl=META-INF/HelloServiceEJB.wsdl -dir=genproxy -package=javacallout -user=scott/scott -endpoint=http://localhost:8888/javacallout/javacallout

The jpub command gives the following output.
Writing genproxy/HelloServiceEJBJPub.java
Writing genproxy/plsql_wrapper.sql
Writing genproxy/plsql_dropper.sql
Writing genproxy/plsql_grant.sql
Writing genproxy/plsql_revoke.sql
Executing: sqlplus scott/tiger<genproxy/plsql_wrapper.sql
Executing: jar cf genproxy/wsdlGenerated.jar -C genproxy javacallout
Executing: loadjava -thin  -user scott/tiger@localhost:1521:sqlj -resolve genproxy/HelloServiceEJBJPub.java genproxy/wsdlGenerated.jar
Writing: genproxy/jpub_proxyload.log
Note: JPublisher loaded the generated Java code and plsql_wrapper.sql into the database as SCOTT.
Note: Please run the SQL script plsql_grant.sql as SYS to grant necessary permissions to SCOTT.
The jpub command generates the Java classes and PL/SQL scripts under the subdirectory genproxy, listed below. We highlight those files that are loaded in the database afer being generated.
  1. genproxy/javacallout: Java classes under this directory are generated by the wscompile tool called by JPublisher.
  2. genproxy/HelloServiceEJBJPub.java: The HelloServiceEJBJPub.java class defines the method sayHello as a static Java method, in order to be invoked from the PL/SQLwrapper.
  3. genproxy/plsql_wrapper.sql: The PL/SQL wrapper for HelloServiceEJBJPub.java.
  4. genproxy/plsql_dropper.sql: remove PL/SQL types and packages defined by plsql_wrapper.sql.
  5. genproxy/plsql_grant.sql: when run under SYS, grant necessary priviliges for the Java client proxy to call the Web Services.
  6. genproxy/plsql_revoke.sql: revoke the privileges granted by plsql_grant.sql.
  7. genproxy/jpub_proxyload.log: the log file for loading those generated Java files and install plsql_wrapper.sql script.
  8. wsdlGenerated.jar: the jar file contains the content of genproxy/javacallout.

Step 6. Grant Privileges

The script plsql_revoke.sql is for SYS to grant necessary privileges for SCOTT to execute the loaded client proxy.
SQL> conn / as sysdba
SQL> @genproxy/plsql_grant.sql

Step 7. Run the Client

To invoke the EJB WebServices from the database, declare and run a PL/SQL block as follows.
SQL> set serveroutput on
SQL> declare
x varchar2(100);
begin
x:=JPUB_PLSQL_WRAPPER.sayHello('Hello from database');
dbms_output.put_line(x);
end;
/
The expected result from the SQLPLUS commands is
HELLO!! You just said : Hello from database

Step 8. Clean up

Remove the PL/SQL wrapper and revoke the granted privileges using SQLPLUS.
SQL> conn scott/tiger
SQL> @genproxy/plsql_dropper.sql
SQL> conn / as sysdba
SQL> @genproxy/plsql_revoke.sql
Remove the loaded Java classes from the database using the dropjava tool.
dropjava -u scott/tiger genproxy/wsdlGenerated.jar