Build a PL/SQL Web Service
By Jason Price

Oracle Database 10g web services allow you to take advantage of your development investment in PL/SQL and SQL while consuming external web services from within the database. Here's how to build your own.

As we've all learned by now, the web services model has proven to be an efficient way to connect applications. Web services protocols such as XML, the de facto standard for information interchange; SOAP (Simple Object Access Protocol), which allows you to exchange XML documents in a distributed environment over HTTP, FTP, and SMTP; WSDL (Web Services Description Language); and UDDI allow you to build platform- and language-independent, loosely coupled distributed applications.

You can provide as well as consume web services in Oracle Database 10g and Oracle Application Server (OracleAS) 10g (through the version of OC4J built into OracleAS). Oracle Database 10g allows you to define the functionality for a web service using PL/SQL (packages, procedures, functions, and triggers), SQL queries, SQL DML statements, and Java stored procedures; OracleAS web services allow you to define functionality for a web service using J2EE (Enterprise JavaBeans, JMS, and so on). You can also expose database functionality as a J2EE web service using OracleAS.

This article, however, focuses strictly on PL/SQL database web services. Oracle Database 10g web services, by definition, expose functionality in the database; they allow you to take advantage of your development investment in PL/SQL and SQL while consuming external web services from within the database.

In this article, you will be introduced to database web services and learn how to create, publish, and invoke a PL/SQL web service for Oracle Database 10g. All the scripts and files referenced in this article are available here.

Required Software

Before you can follow along with the examples shown in this article, you will need to install the following software:

• Oracle Components for J2EE (OC4J) version 9.0.3 or above; you will need the standalone full version (also known as OracleAS Java Edition)
• Oracle Database 10g
• Oracle Net 10g
• Oracle SQL*Plus (included with the client or enterprise installation software)
• Java Software Developer's Kit (SDK) version 1.4 or above

Database Schema Setup

First, we need to set up our database schema, which is a simplified example of a web store.

Before publishing the web service that you will see later, you must first create a user named store and grant the required privileges to that user as follows (you must first log into the database as user with the CREATE USER privilege to create a user):

CREATE USER store IDENTIFIED BY store;
GRANT connect, resource TO store;

Note: You will find these statements and the others shown in this section to set up the store schema in the file web_services.sql.

The next two statements connect as the store user and create a sequence named order_sq that is used later to populate the primary key of the orders table:

CONNECT store/store;
CREATE SEQUENCE order_sq;

The following statements create the four required database tables named product_types, products, customers, and orders:

CREATE TABLE product_types (
  product_type_id INTEGER
    CONSTRAINT product_types_pk PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);

CREATE TABLE products (
  product_id INTEGER
    CONSTRAINT products_pk PRIMARY KEY,
  product_type_id INTEGER
    CONSTRAINT products_fk_product_types
    REFERENCES product_types(product_type_id),
  name VARCHAR2(30) NOT NULL,
  description VARCHAR2(50),
  price NUMBER(5, 2)
);

CREATE TABLE customers (
  customer_id INTEGER
    CONSTRAINT customers_pk PRIMARY KEY,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  dob DATE,
  phone VARCHAR2(12)
);

CREATE TABLE orders (
  order_id INTEGER
    CONSTRAINT orders_pk PRIMARY KEY,
  product_id INTEGER
    CONSTRAINT purchases_fk_products
    REFERENCES products(product_id),
  customer_id INTEGER
    CONSTRAINT purchases_fk_customers
    REFERENCES customers(customer_id),
  quantity INTEGER NOT NULL
);

Note: If you create the tables in a different schema to store, you will need change the schema name in the sample configuration files that you will see later.

The product_types table is used to store the names of the product types that may be stocked by an example online store, the products table contains detailed information about the products sold, the customers table stores information on customers who are allowed to place orders from the store, and the orders table contains details of which customer placed orders for products.

The following INSERT statements add rows to the customers, product_types, and products tables:

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  1, 'John', 'Brown', '01-JAN-1965', '800-555-1211'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  1, 'Book'
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  1, 1, 'Modern Science', 'A description of modern science', 19.95
);

COMMIT;

We will use PL/SQL to implement our database web service code. Your PL/SQL must be placed within a package before you can publish it as a web service. The statements in Listing 1 create a PL/SQL package that contains a procedure that allows a customer to place an order for a product. The package is named dbfunc and contains a procedure named place_order() that accepts the ID of a product, the ID of the customer, and the quantity of the product to be purchased.

As you can see, the place_order() function checks if the product ID and customer ID exist in the products and customers tables, and if so adds a row to the orders table and returns a string containing the ID of the order (which is the value generated by the order_sq sequence). If the customer ID or product ID is invalid then a string containing the error message "No such customer" or "No such product" is returned.

Now, let's examine how to install and configure OC4J.

Installing and Configuring OC4J

You must install OC4J using the following example commands (I used the Windows 2000 command-prompt tool when preparing this article; you can use similar commands in a Unix or Linux shell by replacing the back-slash with a forward-slash in the directory path):

e:
cd e:\oracle\oc4j\j2ee\home
java -jar oc4j.jar -install

You will then be prompted to enter the password for the admin account. I use welcome as my password.

You need to define a data source in the data-sources.xml file located in the oc4j\j2ee\home\config directory by adding a section similar to the following data-source element:

<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="store"
  password="store"
  url="jdbc:oracle:thin:@localhost:1521:ORCL"
  inactivity-timeout="30"
/>

Note: You will find the example data-sources.xml file for this article on the OTN web site. You may need to change the name of the Oracle database service—mine uses the default ORCL service identifier (SID) and runs locally, and Oracle Net listens on port 1521 for connections. If your database runs on a different server, has a different port number, or a different SID, then you will need to modify your data-source element accordingly. Your DBA can give you appropriate connection details for your database. Also, if you created the example database tables in a different schema then you will need to change the username and password in your settings in the data-sources.xml file.

Next, you need to start OC4J via the following commands:

e:
cd e:\oracle\oc4j\j2ee\home
java -jar oc4j.jar

You will see a confirmation message indicating OC4J is started.

Publishing a PL/SQL Package as a Database Web Service

Next, let's examine how to publish the dbfunc PL/SQL package as a web service.

In essence, a database web service uses the functionality in OC4J to expose a Java class— your Java class acts as a wrapper around your actual implementation of the web service in the database. Your implementation can be written in SQL or Java stored procedures as well as PL/SQL.

You may publish a PL/SQL package using the web services assembler tool, which comes with OC4J version 9.0.3 or higher. You need to pass a config.xml file to the web services assembler tool; config.xml contains information about the PL/SQL package in the database schema you want to publish as a web service. Listing 2 contains the example config.xml file used in our example. (You can also use JDeveloper to publish a web service, but that's a different article: "Web Services Development Made Easy," by Elangovan Balusamy.)

You will need to change the entry in the db-url element if your database runs on a different server, has a different port number, or a different SID from localhost, 1521, and ORCL respectively. The httpServerURL entry has the default server and port number on which OC4J runs as localhost on port 8888. If your OC4J runs on a different server and port you will need to edit your config.xml file. (Note: You can find the server and port settings in the http-web-site.xml file in the j2ee\home\config directory where you installed OC4J. Also, if you created the example database tables in a different schema, you will need to change the schema in your settings in the config.xml file.)

To publish dbfunc as a web service you run the following commands:

set ORACLE_HOME=E:\oracle\oc4j

set 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 
  G:\OTN_articles\dws\config.xml

java -jar %ORACLE_HOME%\j2ee\home\admin.jar 
  ormi://localhost 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

The first two commands set the required environment variables named ORACLE_HOME and CLASSPATH. You will need to change your setting of ORACLE_HOME to the directory where you installed the full stand-alone version of OC4J. The CLASSPATH variable contains the location of the required JAR files for deploying a web service. The third command uses the WebServicesAssembler JAR file to publish the web service using the config.xml file. (You will need to change the directory in the -config option to the location where your config.xml file is stored.) The fourth command deploys the web service with the deployment name of "dbfunc." (If you use a different password than "welcome" for your OC4J admin account you will need to set it accordingly.) The fifth command binds the dbfunc web service to the default web site named http-web-site with the path /plsqlsample. (Note: If you are deploying to OC4J built into Oracle 9iAS, in the fifth command you will need to replace http-web-site with default-web-site.

Now that you have successfully published your web service, it's time to invoke it. Let's do that next.

Invoking the Database Web Service

You can use a web browser to invoke your web service. To do that, point your browser to the following location:

http://localhost:8888/plsqlsample/dbfunc

(Note: If your OC4J runs on a different server and port from localhost and 8888 you will need to replace them with your appropriate settings. Also, if you are deploying to OC4J built into Oracle 9iAS, you will need to get the port settings in the configuration files.)

Figure 1 shows the web page that is displayed from my computer.

If you click on the Service Description link you will see the XML containing the WSDL description for the web service. (See Listing 3.)

As you can see, Listing 3 contains the WSDL description for the web service. The following lines in the WSDL show the input parameters and one output parameter:

- <message name="placeOrderInput">
  <part name="param0" type="xsd:decimal" /> 
  <part name="param1" type="xsd:decimal" /> 
  <part name="param2" type="xsd:decimal" /> 
  </message>
- <message name="placeOrderOutput">
  <part name="output" type="xsd:string" /> 
  </message>

The three input parameters are the numbers that will be passed to the place_order() PL/SQL function; these three numbers are the product ID, customer ID, and quantity respectively. The output parameter is the string returned by the place_order() function containing the message that indicates whether placing the order was successful.

Going back the web page shown in Figure 1, you should click the placeOrder link in your browser. The next web page is shown in Figure 2; you should go ahead and enter the three required numeric parameters to the placeOrder() function as I've shown in Figure 2: I've entered 1, 1, and 2 for the product ID, customer ID, and quantity respectively.

Next, you should click the Invoke button to actually run the web service. Execution is performed via SOAP. After a short delay while the web service starts and is executed, you will see the resulting output in your browser, an example of which is shown in Figure 3.

From Figure 3 you

Next Steps: Download Database Web Services Samples and How-To's Read the OC4J 10g (10.0.3.0.0) Developer Preview Enterprise Web Services How-To Download Oracle Database 10g Download OC4J 10g (10.0.3) Developer Preview 2 (for development and test environments only) Visit and bookmark the Oracle Database Web Services Page

can see that the SOAP message from the web service is displayed in your browser, and the output string from the place_order() function is set to "Order placed with ID of 1" in the xsd:string element. The value of 1 comes from the order_sq sequence in the database. Note: If you can the web service multiple times you will see the value increase by one each time.

Behind the scenes in the database, the place_order() function inserts a row in the orders table. You can see this row by using SQL*Plus to log into the database as the store user and running the following SELECT statement:

SQL> SELECT * FROM orders WHERE order_id = 1;

  ORDER_ID PRODUCT_ID CUSTOMER_ID   QUANTITY
---------- ---------- ----------- ----------
         1          1           1          2

Note that the various column values for this row are set to the expected values.

If you attempt to place an order for a non-existent product (for example, a product with an ID of 3) using the web service, you will see the output shown in Figure 4.

From Figure 4 you can see the returned string from the place_order() function is "No such product". If you try placing an order for a non-existent customer then you will see the returned string "No such customer." In each case, no row is inserted into the orders table because the order is invalid.

You can also consume a web service from within the database. This approach allows your database to perform tasks such as tracking, aggregating, and refreshing data generated by a remote web service. You may consume web services via SOAP, and use the Oracle Java Virtual Machine or PL/SQL to invoke the remote web service. You may pass parameters out to the web service from the database and define a virtual database table that you can use to access data returned by the web service. For further details, refer to Oracle Database Java Developer's Guide 10g Release 1.