In-Depth

Integrate Windows Apps With Oracle

Publish a PL/SQL procedure in Oracle as a Web service and consume it from a Web service client in WCF.

• By John Charles Olamendy Turruellas
• August 1, 2007

A common scenario for using Web services is in the creation of business-to-business (B2B) systems where you must expose some business functions as services using Web technologies that users access from outside your network boundary. Typically, you use HTTP (port 80) or HTTPS (port 443) to pass messages through firewalls and DMZs because proprietary protocols ports are disabled by default.

Web services let you integrate independent functions within the enterprise while supporting distributed transactions. I'll explain how to implement this kind of functionality, covering how to publish a Procedural Language/SQL (PL/SQL) procedure in an Oracle Database as a Web service, as well as how to consume it from a Web service client in Windows Communication Foundation (WCF).

The process for making this work is a little tricky, and it's easy to make a misstep, especially when the approaches for making this work aren't well documented. So, I'll walk you through the exact processes required to accomplish this successfully. Specifically, I'll explain the basics of WCF and then show you how to integrate your apps running in both Windows and Oracle. Along the way, I'll provide tips to help you develop your server-side code, including how to connect to an Oracle Database, create a PL/SQL procedure, and expose that functionality using Web services. I'll also show you how to publish the stored procedure as a Web service using JDeveloper, as well as how to create connections to Oracle Containers for J2EE (OC4J) and an Oracle Database. Finally, I'll explain how to develop the client-side components, including the UI.

In Oracle relational database management systems, you specify your application's business logic using PL/SQL stored procedures. Oracle Database 10g and Oracle Application Server (Oracle AS) allow you to define a Web service and expose database functionality through the version of OC4J built into Oracle AS.

To implement this solution, it's critical that you have a solid understanding of WCF. WCF is Microsoft's unified programming model for building and running service-oriented applications. This new communication subsystem lets you build secure, reliable, transacted solutions that interoperate with applications on different platforms. WCF unifies several programming models such as Web services, .NET Remoting, distributed transactions, and message queues into a single, service-oriented model, thus reducing complexity.

Interoperability using Web services is the essential characteristic of WCF. The fundamental communication mechanism is based on Web services specifications such as SOAP, XML, XSD, WSDL, and newly established standards including the WS-* protocols. These specifications address several areas, including basic messaging, security, reliability, transactions, and working with a service's metadata.

WCF also provides many facilities for developing services such as hosting, service instance management, asynchronous calls, reliability, transaction management, disconnected queue calls, and security. It also implements an extensible model that you can use to extend basic features spanning transports, security systems, messaging patterns, encoding, network topologies, and hosting models.

Develop the Server-Side Code
This article's example follows a common business scenario in which a client needs to integrate applications running an Oracle database under Windows. You can expose, consume, and orchestrate all the functionality using standard interfaces such as Web services and a robust programming model.

Let's connect to the Oracle sample database using the scott/tiger account and create a PL/SQL procedure that fetches information for one employee from the emp table and exposes that functionality using Web services. A client application consumes the service and shows the underlying information in its main Windows form.

Before you can follow along with this example, you need to install the software listed in the more information box at the bottom of this article. First, connect to an Oracle Database and create a package and a stored procedure. Connect to the database by going to the command window and running the sqlplusw /nolog command. When the SQL*Plus console appears, type the statement connect scott/tiger. Now you're connected to the default instance—in this case, the ORCL instance.

Next, create the EmpPackage package and the spSelect-EmployeeById stored procedure, which receives an employee number and sends out the employee's name, job, and hire date (Listing 1). PL/SQL is the Oracle extension to SQL that allows you to code procedures and functions. You must place the procedure inside a package for publishing as a Web service. The first statement in Listing 1 creates the PL/SQL package EmpPackage and specifies the procedure's signature. You define the datatype by reference for each parameter according to the data type of the column. For example, in the sp-SelectEmployeeById procedure the v_empno in emp.empno%type signature means that the v_empno parameter is the same type as the empno column in the emp table. Defining datatypes by reference is extremely useful and decreases the amount of maintenance in programs.

The "/" character tells SQL*Plus to execute the PL/SQL statements. The second statement in Listing 1 creates the PL/SQL package body and the implementation of the procedure. The select statement stores the values of the ename, job, and hiredate columns from the emp table into the v_ename, v_job, and v_hiredate output parameters.

Connect to OC4J and Oracle
Now you can publish this stored procedure as a Web service using JDeveloper and its underlying technologies. JDeveloper provides features to create and deploy Web services as well as a little embedded OC4J for testing the Web services.

To start the embedded OC4J, open a command window, change to the %JDEVELOPER_HOME%\jdev\bin directory, and type start_oc4j. When you run this command for the first time, you'll be asked for one password to manage the application server. After entering a password, wait until you see the message "Oracle Containers for J2EE 10g (10.1.3.1.0) initialized." You can then minimize the window, but don't close it.

Next, open JDeveloper and create a connection to OC4J. Go to View | Connection Navigator, open the Connection Navigator window, right-click on the Application Server node, select New Application Server Connection, and click on Next on the Welcome page. On the Type page, enter a descriptive name for the connection, such as oc4jconn, and click on Next. On the Authentication page, enter the password for oc4jadmin user (you chose this password when you started OC4J the first time). Select Deploy Password, and click on Next. On the Connection page, click on Next, and on the Test page, click on Test Connection.

To create a connection to an Oracle Database, go to Connection Navigator, right-click on Database node, select New Database Connection, and click on Next on the Welcome page. On the Type page, enter a descriptive name for the connection, such as oracleconn, and click on Next. On the Authentication page, enter scott in the Username field and tiger in the Password field. Select Deploy Password and click on Next. On the Connection page, set the following values: Driver=Thin, Hostname={the address or DNS where the database system resides}, JDBC Port:1521, and SID=ORCL. Click on Next, and then click on Test Connection on the Test page.

Next you create and deploy the PL/SQL Web service. To create an application and a project, go to View | Application Navigator from the main menu, and open the Applications Navigator window. Right-click on the Applications node, select New Application, enter a descriptive name for the application such as PLSQLWS_App, and click on OK. In the Create Project dialog, enter a name for this project such as PLSQLWS_Proj (in JDeveloper, one application contains several projects, and one project is contained in just one application).

In the Applications Navigator window, right-click on the PLSQLWS_Proj node, and select New… from the context menu. In the New Gallery window on the Categories tree, navigate to the Business Tier | Web Services tree node, and choose PL/SQL Web Service (Figure 1). Click OK to open the Web service creation's wizard.

[Click on image for larger view.]

Figure 1. Create the PL/SQL Web Service. In the New Gallery windows on the Categories tree, navigate to the Business Tier | Web Services tree node and choose PL/SQL Web Service.

In the Select J2EE Web Service Version page, select J2EE 1.4 (JAX-RPC) and click on Next. On the Welcome page, click on Next, and on the Database Connection and Package page, set the following values: Database Connection=oracleconn (the created connection to the database system), Database Package=EMPPACKAGE (the created package as shown in Listing 1), Web Service Name=EmployeeWService, and Java Package=com.plsqlpackage. Select the SOAP 1.1 Binding option, and click on Next. On the Message Format page, click on Next, and on the Program Units page, select the SPSELCTEMPLOYEEBYID procedure and click on Next. On the Type Mapping Options page, click on Next, and on the User Defined Type Mapping page, click on Next. Finally, click on Next on the Handler Details page to finish the creation of this Web service.

To deploy the Web services on OC4J, in the Applications Navigator right-click on the WebServices.deploy file on the Resources node in the project, and select Deploy to|oc4jconn. Next, open a browser, go to the OC4J Administration page at http://localhost:8888/em, enter the password, and look up the Web service test page (Figure 2).

[Click on image for larger view.]

Figure 2. View the Web Service Test Page. Look up the Web service test page by navigating to the OC4J Administration page at http://localhost:8888/em and entering a password.

Develop the Client Side Components
Now you develop the client-side components that interact with the server-side services. The client will contain the Windows Forms application that consumes the PL/SQL Web service functionality through WCF technology. Open VS .NET, select File | New | Project, and navigate in the Project Types tree to the Windows node. Select Windows Application from Templates, and then enter descriptive names for the project, solution, and directory where you'll store the underlying files.

Open a command window, go to the directory where the project files resides, and execute the "svcutil http://localhost:8888/PLSQLWS_App-PLSQLWS_Proj-context-root/EmployeeW-ServiceSoapHttpPort?wsdl" command where you can change the localhost reference for the server name where the PL/SQL Web service is hosted. The svcutil.exe tool lets you create the WCF proxy classes, contracts, and configuration files for accessing the Web services. In this case, you pass the URL as a parameter where the WSDL document resides, which contains the metadata that describes all the information you need to reach the endpoint. The WCF proxy creation isn't integrated with VS .NET, so you need to open a command window and call the svcutil.exe tool outside of your development environment.

Next, you attach the file that contains the generated proxy to the project. Go to Solution Explorer, and click on the Refresh button to display the generated files. Right-click on the Employee-WService.cs file, and select Include In Project. Add an application configuration file (App.config) to your project, and copy in all the content from output.config. The configuration file contains the information about the remote services, endpoints, addresses, contracts, operations, and bindings. You can customize this file manually according to your requirements. Finally, add a reference to the System.ServiceModel and System.Runtime.Serialization assemblies containing the objects that implement the communication mechanism of the WCF technology.

Now you design the user interface. Add two Label controls to show descriptive messages to the user. Then add two Textbox controls—one to get the employee number, and the other (read-only) to show the employee's information. Finally, add one Button control to send a request to the PL/SQL Web service (Listing 2).

Now create an instance of the WCF proxy class Employee-WServiceClient (objProxy), and then send a message to the service using the spselectemployeebyid method, passing the employee number as a parameter. The objResponse object instance of the EmployeeWServiceUser_spselectemployeebyid_Out class receives and references a response message. A formatted output shows the name, hire date, and job of the specific employee. You can access this information through the response message's properties (venameOut, vhiredateOut, and vjobOut). It's a best practice to always close the proxy when the client is no longer using it. This closes the connection and the session with the service.

The configuration file generated by the svcutil.exe tool (Listing 3) describes the location and the contracts provided by the service's endpoint, as well as the underlying communication mechanism to access it. This file lists all service endpoints.

Now that you have a template for creating interoperable, service-oriented applications running on different platforms, you can adapt this article's example to your own situation. You can create your own PL/SQL packages and procedures to specify your particular business logic needs by taking advantage of the data type reference mechanisms. Then, you can expose the stored procedures as Web services to be consumed by clients inside and beyond the boundaries of your organization. Today, it's common to consume Web services inside the company using a workflow engine that executes the business process and then aligns the activities inside the process with Web services. Be sure to check out the online sample application and pick it apart for inspiration.