Table Functions-Turning external Web Services into SQL data source using Table Function - Readme

This document is organized into following sub-sections :

• Overview of the sample application
• Application Architecture
  
• User Notes with Screen Shots
• Dependencies 
• Installation and Configuration
• Description of Sample files

The sample implements a weather reporting system that can be used by the end users to get the temperature report of some cities in US. The application maintains temperature of important US cities in the database table. The end user can access the application's JSP where the user can select city name(s) to get the temperature. The user has the 2 option to view the temperature

a. Recently Collected Temperature
In this case the temperature of the cities is obtained from the database. The database is updated with the latest temperature from an external web service at an interval of 6 minutes. A table function is used to provide the external web service data in the form of a relational table. This table function gets the temperature of each city using a Java Stored Procedure and renders them as a row of data. By storing the temperature in the database, the end user response is quick. Also we can find the highest and lowest temperatures of a day.

b. Current  Temperature
In this case the temperature is obtained from the web service through the table function and the Java Stored Procedure. The table function provides the temperature as a virtual table, thus enabling us to use aggregate functions like avg, min and max on the data.

The user can click on 'Help' link to see how the sample works.

Table functions are functions that produce a collection of rows that can be queried like a physical database table. A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on demand. This makes it possible to use a table function like a virtual table. Pipelined table functions can be multithreaded, and execute concurrent  table functions. It also reduces response time and memory usage, by returning data in streams. 

Pipelined table functions can be implemented in two ways:
1. Native PL/SQL approach: The consumer and producers can run on separate execution threads (either in the same or different process context) and communicate through a pipe or queuing mechanism.
2. Interface approach: The consumer and producers run on the same execution thread. Producer explicitly returns the control back to the consumer after producing a set of results. In addition, the producer caches the current state so that it can resume where it left off when the consumer invokes it again.
In this sample, Native PL/SQL approach is used to implement the table function.

Note: In this sample 'Weather information' and 'temperature' are used interchangeably. Weather information contains only temperature

The architecture of the application is shown in the figure1.1 below. The steps in the application can be explained as

a. Getting weather information from Database
1a. The end user accesses the application's JSP and selects city name(s). 

2a. A Java Server Page queries the database to get the weather information and displays it to the end user.

Every 6 minutes the weather information in the database is updated. The following steps takes place during each update.

1. Job: PL/SQL procedure 'updateDatabase', scheduled to run every one hour (using DBMS_JOB), calls the table function 'TO_TABLE' with a REFCURSOR of zipcodes as input. The table function  is a pipelined one that returns one row of information at a time. This table function processes a row of information, in the REFCURSOR, at a time and returns the processed information in a streamed manner. The table function is PARALLELL_ENABLED by partitioning the input REFCURSOR randomly.

2. Weather Data, as Virtual Table: The table function 'TO_TABLE'  calls the 'GetTemp()' Java Stored Procedure (through its PL/SQL Call Spec wrapper)  to get weather information for each city specified in the REFCURSOR and renders the data as a virtual table .

3. Calling-out Weather Web service: 'GetTemp()' function is a web service client. It takes a zip code as input and returns weather information about the city. The 'GetTemp()' function calls the Weather web service located at http://www.xmethods.net/sd/2001/TemperatureService.wsdl to get the weather information.

4. Refresh, Aggregate and Consolidate Weather Data: The weather information returned by the table function is used to update the WEATHER table. The table functions finds the Minimum and Maximum temperature for each city of a day, which is not available in the information returned by the web service.

b. Getting weather information from Web Service
1b. The end user accesses the application's JSP and selects city names.  A Java Server Pages queries the table function 'TO_TABLE' to get the weather information with a REFCURSOR of zipcodes as input.

2b. The table function calls the 'GetTemp()' Java Stored Procedure  to get weather information for each city specified in the REFCURSOR.

3b.  'GetTemp()' function get the weather information from the web service 

Figure 1.1- Architecture

The First screen, Index.jsp file, provides the end user with a list of cities from which the user can select city name(s) to get the weather information. The list of cities is populated from the database.

    Figure 1.2- First Screen

When the user clicks on 'Weather from Database' button, the form values from the first screen is submitted to the Java Server Page 'GetTemp.jsp'. This JSP gets the weather information of the requested city from the database and displays it to the user as shown in Figure 1.3

    Figure 1.3 - Second Screen

When the user clicks on 'Weather from Web Service' button on the the first screen, the form values from the first screen is submitted to the Java Server Page 'Dynamic.jsp'. This JSP gets the weather information of the requested city from the web service and displays it to the user as shown in Figure 1.4

    Figure 1.4 - Third Screen

Dependencies

Back To Top

This sample uses the Weather Information web services at http://www.xmethods.net/sd/2001/TemperatureService.wsdl

This section explains the steps to be followed to install and configure the sample application.

• Required Software
• Notation used
• Extracting the source code
• Configuring the application
• Deploying and Running the application
• From SQL Plus
  
• From Oracle9i JDeveloper
• From stand-alone OC4J

Required Software

• Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from the Oracle Technology Network.
• Oracle9iAS Containers for J2EE (OC4J), 9.0.2 or later (available here ) along with JDK 1.2 or higher ( available here)
  or
  Oracle9i JDeveloper version 9.0.3 or later available here
• If you wish to deploy the application to standalone OC4J then you must download and install the Jakarta Ant utility version 1.4

Notation Used

This section will describe the terms used in the document.

For example,

• %SAMPLE_HOME%- The directory where the TableFunctionSample.jar file was unzipped to. This directory contains the TableFunctionSample directory



• %OC4J_HOME% - The directory where OC4J is installed. For example, if oc4j_extended.zip was unzipped to the C:\OC4J , then %OC4J_HOME% would be C:\OC4J.



• %JDEV_HOME% - The directory where Oracle9i JDeveloper is installed.

• %ORACLE_HOME% - The directory where the Oracle9i Client or Database software is installed

• %ANT_HOME% - The directory where the Ant distribution has been unpacked.

Extracting the source code

The application files are stored in an archive named TableFunctionSample.jar. Download the archive and extract it to a convenient directory (%SAMPLE_HOME%). Execute following command to extract the files:

jar xvf TableFunctionSample.jar

The archive TableFunctionSample.jar extracts the files that implement the Table function demo. All the files are extracted into the TableFunctionSample directory. This directory will have all the sources as described later.

Configuring the application

1. This demo requires some database tables to be created in the database. The script for creating the required database objects and populating them with sample data is provided in the file CreateTable.sql located in the directory %SAMPLE_HOME%/TableFunctionSample/config.

Edit this file to specify your proxy server and port. You will need to modify this line :

execute dbms_java.grant_permission('TF','SYS:java.net.SocketPermission','<proxy-host>:<proxy-port>','connect,resolve');

For example,

execute dbms_java.grant_permission('TF','SYS:java.net.SocketPermission','gatekeeper.idc.oracle.com:80', 'connect,resolve');

2. You need to execute the script CreateTable.sql. Connect to your database as any user and run the following command from the SQL prompt

    SQL>@%SAMPLE_HOME%/TableFunctionSample/config/CreateTable.sql

This creates a new database user "TF" (password - "TF") with all the required tables and privileges.

3. Ensure that %ORACLE_HOME%\bin is in the system path. This demo requires some jar files to be loaded to the database. This is done from a command prompt as shown below :

loadjava -thin -user sys/<sys-password>@<hostname>:<port>:<SID> -resolve -synonym -verbose -grant public %OC4J_HOME%/soap/lib/soap.jar %OC4J_HOME%/lib/dms.jar %OC4J_HOME%/jlib/javax-ssl-1_1.jar %ORACLE_HOME%/lib/servlet.jar %OC4J_HOME%/jdk/jre/lib/ext/mail.jar

For example,

loadjava -thin -user sys/change_on_install@insn104a.idc.oracle.com:1521:otn9i -resolve -synonym -verbose -grant public c:\soap\lib\soap.jar c:\lib\dms.jar c:\jlib\javax-ssl-1_1.jar c:\orahome\lib\servlet.jar c:\jdk\jre\lib\ext\mail.jar

This will load all the required classes in the sys schema and create public synonyms for them.

Note : This step may take some time to complete.

4. Edit the file JSPClient.java located in the directory %SAMPLE_HOME%\TableFunctionSample\src. You need to change the proxy port and host with values specified in Step 1. Edit the following lines :

/*
* Change these variables values with your Proxy Host and port
*/
private String proxyHost = "<proxy-host>";
private int proxyPort    = <proxy-port>;

For example,

private String proxyHost = "gatekeeper.idc.oracle.com";
private int proxyPort    = 80;

5. Ensure that %ORACLE_HOME%\bin is in the system path. This demo requires the Web Service Client to be loaded to the database. This is done from a command prompt as shown below :

loadjava -thin -user tf/tf@<hostname>:<port>:<SID> -resolve -verbose %SAMPLE_HOME%/TableFunctionSample/src/JSPClient.java 

where,

For example,

loadjava -thin -user tf/tf@insn104a.idc.oracle.com:1521:otn9idb -resolve -verbose c:\TableFunctionSample\src\JSPClient.java

6. The Java Stored Function loaded in the previous step needs to be published and the Table function and database job has to be created to invoke the Web Service. This is done by the CreateProcedure.sql

Connect to your database as any user and run the script CreateProcedure.sql at the SQL prompt :
SQL>@%SAMPLE_HOME%/TableFunctionSample/config/CreateProcedure.sql

This sql script publishes the stored procedure created in the previous step to the database and creates the table function and a job that invokes the table function once an hour.

7.Edit DBConnection.jsp file located in the directory %SAMPLE_HOME%/TableFunctionSample/webroot. Change the hostname, SID and Port to those of the database instance you want to connect to. Edit the following lines

<!-- Change the hostname, port and sid to the database you want to connect -->
<% 
String DBURL = "jdbc:oracle:thin:@<hostname>:<port>:<sid>";
%>

   For Example,

<!-- Change the hostname, port and sid to the database you want to connect -->
<% 
String DBURL = "jdbc:oracle:thin:@insn104a.idc.oracle.com:1522:ora9idb";
%>

Deploying and Running the application

Running the application using Oracle9i JDeveloper

Running the application using OC4J

Running the sample from SQL Plus

You can see the working of the table functin and the Java Stored Procedure by giving SQL queries like the following after connecting to 'tf' user.

SQL> SELECT avg(temp), min(low_temp),max(high_temp) 
FROM TABLE(weather_pack.TO_TABLE (CURSOR(SELECT zipcode FROM weather )));

Running the application using Oracle9i JDeveloper

1. Open Oracle9i JDeveloper.

2. Navigate to %SAMPLE_HOME%/TableFunctionSample

3. Open the file TableFunctionSample.jws and expand TableFunctionSample.jws node by clicking it.

4. Expand TableFunctionSample.jpr node by clicking it.

5. You can run the application from JDeveloper simply by clicking on Run TableFunctionSample.jpr on the Run Menu.

6. You can select a city(or multiple cities) and get the weather information about the city from the database or from the web service.

Deploying and Running the application using OC4J

1.Ensure that %ANT_HOME%\bin is in the system PATH. Also have OC4J_HOME environment variable point to %OC4J_HOME%. Use the following command from %SAMPLE_HOME%/TableFunctionSample to generate the EAR file -

ant -buildfile build.xml

2. Start OC4J .[ java -jar oc4j.jar from %OC4J_HOME%/j2ee/home]

3. Deploy the EAR from %OC4J_HOME%\j2ee\home using

java -jar admin.jar ormi://<host>:<port> <uid> <password> -deploy -file %SAMPLE_HOME%/TableFunctionSample/TableFunctionDemo.ear -deploymentName tablefunction

This will deploy the application with name "tablefunction"


Now, bind the web application to the web site using the following command,

java -jar admin.jar ormi://<host>:<port> <uid> <password> -bindWebApp tablefunction TableFunctionDemo http-web-site /tablefunction

where,

http-web-site	the website to bind the web application
host	Host/ IP of this site
port	Port of this site
uid	admin user id
password	admin password

4. This completes the deployment to the standalone OC4J server. You can access the application following step 5

Note : To undeploy the application use

java -jar admin.jar ormi://localhost:<port> <uid> <password> -undeploy tablefunction

5. Open a browser and type the URL to access the application. The URL should be of the following type

http://<server_host_name>:<port>/tablefunction/Index.jsp
where <server_host_name> : The host name of the application Server. For ex. incq185b.idc.oracle.com
and <port> : Port No. on which the server is hosted. By default this value is 8888.

6. You can select a city(or multiple cities) and get the weather information about the city from the database or from the web service.