Accessing Web Service from the Database

Accessing Web Services from the Database

Purpose

This tutorial describes how to set up Oracle Database 10g to be a consumer of existing Web services.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following:

	Overview
	Prerequisites
	Installing Oracle SQLJ
	Setting Up Your Environment Variables
	Installing the Call-Out Utility
	Calling the Web Service
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

This tutorial shows you how to call existing Web services from your Oracle database. A Web service is a piece of software that provides functionality and accessibility to client applications, using standard XML-based messaging protocol (i.e., SOAP) over Web protocols (i.e., HTTP).

In this tutorial, you learn how to invoke existing Web services from within the database using only a SQL statement. There is no need to learn SOAP, WSDL, UDDI, and HTTP. Most Web services can be used if it makes sense from the database perspective. This tutorial provides a complete step-by-step guide to configuring the database and the client-side tools, locating the Web service, identifying its call sequence, and invoking it.

Typical public Web services include stock quotes, weather reports, credit card report and verification.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.	Perform the Installing Oracle Database 10g on Windows tutorial.

Back to Topic List

Installing Oracle SQLJ

To perform this tutorial, you need to have SQLJ installed. Perform the following steps:

1.	Download and unzip the Oracle Database 10g Client CD.
2.	Execute the installer via setup.exe.
3.	At the Welcome window, click Next.
4.	Select Custom for the Installation Type and click Next.
5.	Make sure your Database Oracle Home (OraDb10g_home1) is selected. Then click Next.
6.	Under the Oracle Client section, select Oracle SQLJ and click Next.
7.	The installation verifies that your system has the minimum requirements. When done, click Next.
8.	Review your selections, then click Install.
9.	The progress window will appear.
10.	Oracle SQLJ has been installed successfully. Click Exit. Then click Yes.

Back to Topic List

Setting Your Environment Variables

In order to install the Web Services Call-Out Utility and access the Web service, you need to make sure your PATH environment variable is set up correctly:

1.	Select Start > Settings > Control Panel. Double-click System.
2.	Click Advanced tab. Then click Environment Variables.
3.	Select Path under System Variables and click Edit.
4.	Add <oracle_home>\jdk\bin to the path and click OK three times .

Back to Topic List

Installing the Call-Out Utility

To install the Web services Call-Out Utility, perform the following steps:

1.	From your browser, access the following URL: http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html
2.	Scroll down to the link labeled Callout Utility for 10g (R1 + R2) RDBMS. Right-click and click Save Link As.
3.	Save the file in your working directory (i.e. c:\wkdir) and click Save.
4.	Double-click dbws-callout-utility-10R2.zip from the location where the file was saved.
5.	Click Extract.
6.	Extract the contents of the file into your <database oracle home> directory. Be certain to set the Use folder names option.
7.	Open a DOS window, set the CLASSPATH environment variable to include the following: %oracle_home%\jdbc\lib\ojdbc14.jar; %oracle_home%\jdbc\lib\orai18n.jar; %oracle_home%\sqlj\lib\translator.jar; %oracle_home%\sqlj\lib\runtime12.jar; %oracle_home%\sqlj\lib\runtime12ee.jar; %oracle_home%\sqlj\lib\dbwsa.jar; %oracle_home%\jdbc\lib\classes12.jar You can use the set_classpath.bat file to do this. For example, if your ORACLE_HOME is set to c:\oracle\product\10.2.0\db_1, the set classpath command would look as follows: cd c:\oracle\product\10.2.0\db_1 set classpath=.;c:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar; c:\oracle\product\10.2.0\db_1\jdbc\lib\orai18n.jar; c:\oracle\product\10.2.0\db_1\sqlj\lib\translator.jar; c:\oracle\product\10.2.0\db_1\sqlj\lib\runtime12.jar; c:\oracle\product\10.2.0\db_1\sqlj\lib\runtime12ee.jar; c:\oracle\product\10.2.0\db_1\sqlj\lib\dbwsa.jar; c:\oracle\product\10.2.0\db_1\jdbc\lib\classes12.jar
8.	Enter the following commands. Make sure you use the correct password for the SYS user. loadjava -u sys/oracle -r -v -f -s -grant public -genmissing sqlj/lib/dbwsclient.jar Note that there are no errors.

Back to Topic List

Calling the Web Service

To call the Web service, perform the following steps:

1.	In your browser, enter the following URL: http://www.cdyne.com/developers/overview.aspx
2.	Click the Get WSDL link for the Phone Verifier service.
3.	Note the URL. It will be used on the jpub command line.
4.	Scroll to where you see the service port address, and note that URL. It will be used as the endpoint on the jpub command line.
5.	Enter the following command at the DOS command prompt. jpub -user hr/hr -sysuser system/oracle -proxywsdl=http://ws.cdyne.com/phoneverify/phoneverify.asmx?wsdl -proxyopts=tabfun -httpproxy=www-proxy.us.oracle.com:80 -endpoint=http://ws.cdyne.com/phoneverify/phoneverify.asmx -dir=phone
6.	Execute the following commands in your DOS command window: sqlplus /nolog connect / as sysdba exec dbms_java.grant_permission('HR','SYS:java.lang.RuntimePermission','setFactory','');
7.	Note the parameters in the wsdl page - there are 2. The site notes elsewhere that for testing purposes, use "0" for the LicenseKey.
8.	To test the service, execute the following commands: connect hr/hr select jpub_plsql_wrapper.checkphonenumber('6505067000','0') from dual;
9.	Because you provided the tabfun option to the jpub program, wrapper functions for calling table versions of the functions have been created. This means you an send a table of parameters. Create a table for this, and populate it with several phone numbers, by executing the following statements while logged in as the HR user: create table phonetab (phone varchar2(10), license varchar2(1) default '0'); insert into phonetab (phone) values('6505067000'); insert into phonetab (phone) values('2054038957'); insert into phonetab (phone) values('7195778000'); insert into phonetab (phone) values('3052607200'); commit;
10.	Execute the following query to see the results for all the phone numbers inserted into the table: select * from table(jpub_plsql_wrapper.to_table_checkphonenumber (cursor(select * from phonetab)));

Back to Topic List

Summary

In this tutorial, you learned how to:

	Install the Web Services Call-Out Utility
	Call a Web Service From the Database Using SQL

Back to Topic List

Place the cursor over this icon to hide all screenshots.