Accessing Web Service from the Database
Accessing Web Services from the Database
This tutorial describes how to set up Oracle
Database 10g to be a consumer of existing Web services.
Approximately 30 minutes
This tutorial covers the following:
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.
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
Before you perform this tutorial, you should:
Back to Topic List
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
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
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
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
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
|
