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

Previous
Previous
Next
Next
 

A Stored Procedure Tutorial

This appendix demonstrates how to create a Java stored procedure and trigger. Topics include:

A.1 Creating a Stored Procedure and Trigger

In this tutorial, you create a Java class EMAIL, load the class into Oracle Database Lite, publish its method to SQL, and create a trigger for the method. The EMAIL class appears in the source file EMAIL.java, and is available in the Java examples directory at the following location.

&fmv132;\Mobile\Sdk\Samples\JDBC

EMAIL has a method named assignEMailAddress, which generates an email address for an employee based on the first letter of the employee's first name and up to seven letters of the last name. If the address is already assigned, the method attempts to find a unique email address using combinations of letters in the first and last name.

After creating the class, you load it into Oracle Database Lite using MSQL. For this example you use the SQL statement CREATE JAVA. Alternatively, you can use the loadjava utility to load the class into Oracle Database Lite. After loading the class, you publish the assignEMailAddress method to SQL.

Finally, you create a trigger that fires the assignEMailAddress method whenever a row is inserted into T_EMP, the table that contains the employee information.

As arguments, assignEMailAddress takes a JDBC connection object, the employee's identification number, first name, middle initial, and last name. Oracle Database Lite supplies the JDBC connection object argument. You do not need to provide a value for the connection object when you execute the method. assignEMailAddress uses the JDBC connection object to ensure that the generated e-mail address is unique.

A.1.1 Start MSQL

Start MSQL and connect to the default Oracle Database Lite. Since the Java application in this tutorial prints to standard output, use the DOS version of MSQL. From a DOS prompt, type:

msql system/mgr@jdbc:polite:polite

The SQL prompt should appear.

A.1.2 Create a Table

To create a table, type:

CREATE TABLE T_EMP(ENO INT PRIMARY KEY,

   FNAME VARCHAR(20), 

   MI CHAR,

   LNAME VARCHAR(20),

   EMAIL VARCHAR(8));

A.1.3 Create a Java Class

Create and compile the Java class EMAIL in the file EMAIL.java in C:\tmp. EMAIL.java implements the assignEMailAddress method. The code sample given below lists the contents of this file. You can copy this file from the following location.

&fmv133;\Mobile\Sdk\Samples\JDBC

import java.sql.*;


public class EMAIL {

   public static void assignEMailAddress(Connection conn,

            int eno, String fname,String lname)

            throws Exception

   {

      Statement stmt = null;

      ResultSet retset = null;

      String emailAddr;

      int i,j,fnLen, lnLen, rowCount;


      /* create a statement */

      try { 

         stmt = conn.createStatement();

      }

      catch (SQLException e)

      {

         System.out.println("conn.createStatement failed: " + 

         e.getMessage() + "\n");

         System.exit(0); 

      }

      /* check fname and lname */

      fnLen = fname.length(); 

      if(fnLen > 8) fnLen = 8;

      if (fnLen == 0) 

         throw new Exception("First name is required");

      lnLen = lname.length(); 

      if(lnLen > 8) lnLen = 8;

      if (lnLen == 0) 

         throw new Exception("Last name is required");

      for (i=1; i <= fnLen; i++)

      {

         /* generate an e-mail address */

         j = (8-i) > lnLen? lnLen:8-i;

         emailAddr = 

               new String(fname.substring(0,i).toLowerCase()+

               lname.substring(0,j).toLowerCase());

         /* check if this e-mail address is unique  */

         try {

            retset = stmt.executeQuery(

                   "SELECT * FROM T_EMP  WHERE email = '"+

                   emailAddr+"'");

            if(!retset.next()) {

               /* e-mail address is unique; 

               * so update the email column */

               retset.close();

               rowCount = stmt.executeUpdate(

                   "UPDATE T_EMP SET EMAIL = '"

                   + emailAddr + "' WHERE ENO = "

                   + eno);

               if(rowCount == 0) 

                  throw new Exception("Employee "+fname+ " " +

                          lname + " does not exist");

               else return;

            }

         }

         catch (SQLException e) {

            while(e != null) {

               System.out.println(e.getMessage());

               e = e.getNextException();

            }   

         }  

      }

      /* Can't find a unique name */

      emailAddr = new String(fname.substring(0,1).toLowerCase() + 

           lname.substring(0,1).toLowerCase() + eno);

      rowCount = stmt.executeUpdate(

           "UPDATE T_EMP SET EMAIL = '"

           + emailAddr + "' WHERE ENO = "

           + eno);

      if(rowCount == 0) 

         throw new Exception("Employee "+fname+ " " +   

              lname + " does not exist");

      else return;

   }

}

A.1.4 Load the Java Class File

To load the EMAIL class file into Oracle Database Lite, type:

CREATE JAVA CLASS USING BFILE 

   ('c:\tmp', 'EMAIL.class');

If you want to make changes to the class after loading it, you need to:

  1. Drop the class from the database, using dropjava or DROP JAVA CLASS

  2. Commit your work

  3. Exit MSQL

  4. Restart MSQL

This unloads the class from the Java Virtual Machine.

A.1.5 Publish the Stored Procedure

You make the stored procedure callable from SQL by creating a call specification (call spec) for it. Since assignEMailAddress does not return a value, use the CREATE PROCEDURE command, as follows:

CREATE OR REPLACE PROCEDURE 

   ASSIGN_EMAIL(E_NO INT, F_NAME VARCHAR2, L_NAME VARCHAR2)

   AS LANGUAGE JAVA NAME 'EMAIL.assignEMailAddress(java.sql.Connection,

int, java.lang.String,

     java.lang.String)';

A.1.6 Populate the Database

Insert a row into T_EMP:

INSERT INTO T_EMP VALUES(100,'John','E','Smith',null);

A.1.7 Execute the Procedure

To execute the procedure, type:

SELECT ASSIGN_EMAIL(100,'John','Smith')

  FROM dual

A.1.8 Verify the Email Address

To see the results of the ASSIGN_EMAIL procedure, type:

SELECT * FROM T_EMP;

This command produces the following output:

     ENO  FNAME                M LNAME                EMAIL

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

     100  John                 E Smith                jsmith

A.2 Create a Trigger

To make ASSIGN_EMAIL execute whenever a row is inserted into T_EMP, create an AFTER INSERT trigger for it. Create the trigger as follows:

CREATE TRIGGER EMP_TRIGG AFTER INSERT ON T_EMP FOR EACH ROW

  ASSIGN_EMAIL(eno,fname,lname);

A trigger named EMP_TRIGG fires every time a row is inserted into T_EMP. The actual arguments for the procedure are the values of the columns eno, fname, and lname.

You do not need to specify a connection argument.

A.2.1 Testing the Trigger

Test the trigger by inserting a row into T_EMP:

INSERT INTO T_EMP VALUES(200,'James','A','Smith',null);

A.2.2 Verify the Email Address

Issue a SELECT statement to verify that the trigger has fired:

SELECT * FROM T_EMP;

   ENO FNAME                M LNAME                EMAIL

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

   100 John                 E Smith                jsmith

   200 James                A Smith                jasmith

A.3 Commit or Roll Back

Finally, commit your changes to preserve your work, or roll back to cancel changes.