Skip Headers
Oracle® Database Lite Developer's Guide for Java
10g (10.0.0)
Part No. B13811-01
  Go To Table Of Contents
Go To Index


B Sample Programs

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

B.1 Java Samples Overview

The &fmv134;\Mobile\SDK\Samples\JDBC directory contains sample programs that demonstrate the use of Java stored procedures, Java Replication Classes, and JDBC with Oracle Database Lite.

The Java examples directory contains these files:

  1. Stoproex.sql



  4. plsqlex.sql


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.

B.1.1 JDBC Sample

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

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

B.1.3 Java Stored Procedures Sample

The Java stored procedures sample shows how to manually attach a class to an Oracle Database Lite table. Alternatively, you can load the class into the Oracle Database 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 4, "Java Stored Procedures and Triggers".

The file Stoproex.sql contains SQL statements to create a sample schema. You must run this script using MSQL 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, 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 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 Database Lite supplies the current connection for any argument of type java.sql.Connection.

The Java method SHIP uses JDBC classes to access Oracle Database Lite. 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

The method SHIP updates the quantity of products to ship. Since SHIP is a non-static method, Oracle Database 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 Database 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.

B.2 Running the Samples

To run the Java samples:

  1. Go to the samples directory, &fmv135;\Mobile\Sdk\Samples\JDBC. For example:

    cd &fmv136;\Mobile\Sdk\Samples\JDBC
  2. Add "." (the current directory) to the CLASSPATH, if it is not already included:

  3. Execute the SQL scripts using the DOS command-line version of MSQL. For example:

    msql system/mgr@jdbc:polite @stoproex.sql

B.2.1 Running the JDBC Sample

To use the JDBC sample, install the PRODUCT table in Oracle Database Lite by running the Stoproex.sql script:

msql system/mgr@jdbc:polite @stoproex.sql

Compile the source file using the command:


Run the compiled class:


B.2.2 Running the PL/SQL Conversion Samples

To run, start MSQL:

msql system/mgr@jdbc:polite

At the MSQL prompt, run the script:


Attach the Java source file 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 for information regarding additional samples. The samples are named sampleOne to sampleFour.

B.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@jdbc:polite @stoproex.sql

select inventory.ship_product(p,q) from dual;

When the script completes, display the contents of the inventory table. At the MSQL prompt, type:

select * from inventory;


---- ---- ---------   

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.