Skip Headers

Oracle9i Lite Developer's Guide for Java
Release 5.0.1
Part No. A95261-01
Go To Table Of Contents
Contents
Go To Index
Index

Previous Next

D
Sample Programs

This document provides instructions for using the sample Java programs provided with Oracle Lite. Topics include:

D.1 Java Samples Overview

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:

  1. Stoproex.sql

  2. INVENTORY.java

  3. JDBCEX.java

  4. plsqlex.sql

  5. PLSQLEX.java

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.

D.1.1 JDBC Sample

The file JDBCEX.java contains a sample Java program that uses JDBC classes to select the rows of the PRODUCT table and display information.

D.1.2 PL/SQL Conversion to Java Samples

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.

D.1.3 Java Stored Procedures Sample

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:

Table Description
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 and CHECK_INVENTORY.

The 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:

  1. 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.

  2. SQL converts 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: "Inventory"."shipProduct".

  3. The connection object is not explicitly given in the arguments to the method. Oracle Lite supplies the current connection for any argument of type java.sql.Connection.

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.

The method 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.

D.2 Running the Samples

To run the Java samples:

  1. Go to the samples directory, Oracle_Home\Mobile\SDK\Examples\Java. For example:

    cd \ORACLE_HOME\MOBILE\SDK\EXAMPLES\JAVA
    
    
  2. Add "." (the current directory) to the CLASSPATH, if it is not already included:

    set CLASSPATH=.;%CLASSPATH%
    
    
  3. Execute the SQL scripts using the DOS command-line version of SQL*Plus. For example:

    mSQL system/mgr@ODBC:polite @stoproex.sql
    
    

D.2.1 Running the JDBC Sample

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:

javac JDBCEX.java

Run the compiled class:

java JDBCEX

D.2.2 Running the PL/SQL Conversion Samples

To run PLSQLEX.java, start SQL*Plus:

mSQL system/mgr@odbc:polite

At the SQL*Plus prompt, run the script:

@plsqlex.sql

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 sampleOne to sampleFour.

D.2.3 Running the Java Stored Procedures Sample

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.


Previous Next
Oracle Logo
Copyright © 2002 Oracle Corporation

All rights reserved
Go To Table Of Contents
Contents
Go To Index
Index