Oracle9i Lite Developer's Guide for Java
Part No. A95261-01
This document provides instructions for using the sample Java programs provided with Oracle Lite. Topics include:
The Oracle_Home\Lite\Examples\Java directory contains sample programs that demonstrate the use of Java stored procedures, Java Replication Classes, and JDBC with Oracle Lite.
The Java examples directory contains these files:
The following sections describe the samples. Java class, method, and file names are case-sensitive. When running Java programs from SQL, you must enclose names in double quotes to preserve their case.
Note:When using JDK 1.1.8, the samples may not produce consistent results in all systems. Using the Symantec JIT compiler may resolve the inconsistency.
The file JDBCEX.java contains a sample Java program that uses JDBC classes to select the rows of the PRODUCT table and display information.
You can convert stored procedures and triggers written in Oracle's PL/SQL language to Java. Several of the Java programs in PLSQLEX.java correspond to PL/SQL programs described in the Oracle Server PL/SQL Users Guide and Reference manual. Plsqlex.sql contains SQL statements that invoke the Java stored procedures.
The Java stored procedures sample shows how to manually attach a class to an Oracle Lite table. Alternatively, you can load the class into the Oracle Lite database using
loadjava, and publish its methods to SQL using the CREATE PROCEDURE or CREATE FUNCTION statements. In this model, you do not attach the class to a database table. For more information on the publish model of developing stored procedures, see "Model 1: Using the Load and Publish Stored Procedure Development Model" in Chapter 2, " Java Stored Procedures and Triggers".
The file Stoproex.sql contains SQL statements to create a sample schema. You must run this script using SQL*Plus before running the Java samples. The sample schema contains the following three tables:
|PRODUCT||Stores information about products.|
|PRODUCT_COMPOSITION||Stores information about the composition of products. Each row of the table keeps track of the quantity of a sub-product required to build the product.|
|INVENTORY||Stores the quantity of products in the warehouse.|
Stoproex.sql also contains statements that insert rows into the tables, attach a Java class to the INVENTORY table, and create an AFTER UPDATE trigger in the INVENTORY table's QTY column.
The Java class attached to the INVENTORY table is defined in the file, INVENTORY.java. It has one static method called
SHIP_PRODUCT, and two non-static (instance) methods called
SHIP_PRODUCT method takes three arguments: a connection object, a product ID, and the quantity of the product to be shipped to the customer.
Stoproex.sql invokes the method with the following SQL statement:
SELECT inventory.ship_product(100,1) FROM DUAL FOR UPDATE;
Notice the following:
Static methods must be referred to as table_name.method_name. The FROM clause for static method execution must always refer to the pseudo table DUAL.
inventory.ship_product into uppercase because the method name is
SHIP_PRODUCT in INVENTORY.java. If you name the table "Inventory" and the method "
shipProduct", you must double-quote both names:
The connection object is not explicitly given in the arguments to the method. Oracle Lite supplies the current connection for any argument of type
The Java method
SHIP uses JDBC classes to access the Oracle Lite database. It creates a statement from the connection passed as an argument and executes a SELECT statement. The SELECT statement executes the Java non-static method
SHIP, also defined in INVENTORY.java.
SHIP updates the quantity of products to ship. Since
SHIP is a non-static method, Oracle Lite creates an instance of the class
INVENTORY before calling this method. It creates the instance using the constructor that takes the columns specified in the WITH CONSTRUCTOR ARGS clause of the ATTACH statement.
Since this sample creates an AFTER UPDATE trigger on the QTY column of the INVENTORY table, each UPDATE executes the
CHECK_INVENTORY method. Since
CHECK_INVENTORY is a non-static method, Oracle Lite uses the row instance or creates a new instance if one does not exist.
If the updated quantity-on-hand drops below the inventory threshold, the
CHECK_INVENTORY method uses the PRODUCT_COMPOSITION table to look up the constituent parts of the product. It also updates the quantity of each to reflect the fact that a certain quantity of this product must be manufactured to replenish inventory. This update happens recursively until an end product is reached, at which point
CHECK_INVENTORY places an order for the product.
To run the Java samples:
Go to the samples directory, Oracle_Home\Mobile\SDK\Examples\Java. For example:
Add "." (the current directory) to the CLASSPATH, if it is not already included:
Execute the SQL scripts using the DOS command-line version of SQL*Plus. For example:
mSQL system/mgr@ODBC:polite @stoproex.sql
To use the JDBC sample, install the PRODUCT table in the Oracle Lite database by running the Stoproex.sql script:
mSQL system/mgr@ODBC:polite @stoproex.sql
Compile the source file using the command:
Run the compiled class:
To run PLSQLEX.java, start SQL*Plus:
At the SQL*Plus prompt, run the script:
Attach the Java source file PLSQLEX.java to the table:
alter table temp attach java source "PLSQLEX" in '.';
To execute the
table method, type:
select temp."sampleOne"() from dual for update;
To view the results:
select * from temp;
See the file PLSQLEX.java for information regarding additional samples. The samples are named
Run the Stoproex.sql script to install the tables and stored procedures required for the stored procedures sample:
mSQL system/ mgr@ODBC:polite @stoproex.sql select inventory.ship_product(p,q) from dual;
When the script completes, display the contents of the inventory table. At the SQL*Plus prompt, type:
select * from inventory;
PID QTY THRESHOLD ---- ---- --------- 100 1 1 101 -6 2 102 -26 8 103 -26 8
Negative numbers in the table indicate that parts 101, 102, and 103 need to be restocked.