2
Java Stored Procedures and Triggers

2.3.1.1 Loading Classes

To load Java classes into the Oracle Lite database, you can use either:

• loadjava

• the SQL CREATE JAVA command

The loadjava command-line utility automates the task of loading Java classes into Oracle Lite and Oracle8i databases. To load Java classes manually, use the SQL CREATE JAVA command.

-force -verbose 

loadjava {-help | -h}

cd \scott\javastuff

jar -cf alpharesources.jar alpha\*.properties

loadjava options alpharesources.jar

loadjava options alpha.jar 

loadjava options \scott\javastuff\alpha.jar

CREATE [OR REPLACE] [AND RESOLVE] [NOFORCE]

   JAVA {CLASS [SCHEMA <schema_name>] |

   RESOURCE NAMED [<schema_name>.]<primary_name>}

   [<invoker_rights_clause>]   

   RESOLVER <resolver_spec>]

   USING BFILE ('<dir_path>', '<class_name>') 

   

2.3.1.2 Publishing Stored Procedures to SQL

After loading the Java class into the Oracle Lite database using loadjava or CREATE JAVA, you publish any static method in the class that you want to call from SQL. To publish the method, create a call specification (call spec) for it. The call spec maps the Java method's name, parameter types, and return types to SQL counterparts.

You do not need to publish every stored procedure, only those that serve as entry points for your application. In a typical implementation, many stored procedures are called only by other stored procedures, not by SQL users.

To create a call spec, use the SQL commands CREATE FUNCTION or CREATE PROCEDURE. Use CREATE FUNCTION for methods that return a value, and CREATE PROCEDURE for methods that do not return a value. The CREATE FUNCTION and CREATE PROCEDURE statements have the following syntax.

CREATE [OR REPLACE]

   { PROCEDURE [<schema_name>.]<proc_name> [([<sql_parms>])] |

   FUNCTION [<schema_name>.]<func_name> [([<sql_parms>])]

   RETURN <sql_type> }

   <invoker_rights_clause>

   { IS | AS } LANGUAGE JAVA NAME 

   '<java_fullname>  ([<java_parms>])

   [return <java_type_fullname>]';

   /

The following apply to this statement's keywords and parameters:

• <sql_parms> has the following format:

  <arg_name> [IN | OUT | IN OUT]
  
           <datatype>  
  
     
  

• <java_parms> is the fully qualified name of the Java datatype.

• For compatibility with Oracle8i, Oracle Lite recognizes but ignores the <invoker_rights_clause> clause.

• <java_fullname> is the fully qualified name of a static Java method.

  
  

  Note: In SQL*Plus, you must follow the CREATE PROCEDURE and CREATE FUNCTION statement with a slash (/).

  
  

For example, assume the following class has been loaded into the database:

import java.sql.*;

import java.io.*;

 

public class GenericDrop {

   public static void dropIt (Connection conn, String object_type, 

                       String object_name) throws SQLException {

      // Build SQL statement

      String sql = "DROP " + object_type + " " + object_name;

      try {

         Statement stmt = conn.createStatement();

         stmt.executeUpdate(sql);

         stmt.close();

      } catch (SQLException e) {

         System.err.println(e.getMessage());}

   }  // dropIt

}  // GenericDrop  

Class GenericDrop has one method named dropIt, which drops any kind of schema object. For example, if you pass the arguments "table" and "emp" to dropIt, the method drops the database table EMP from your schema.

The following call specification publishes the method to SQL:

CREATE OR REPLACE PROCEDURE drop_it (

      obj_type VARCHAR2,

      obj_name VARCHAR2)

   AS LANGUAGE JAVA

   NAME 'GenericDrop.dropIt(java.sql.Connection, 

      java.lang.String, java.lang.String)';

   /

Notice that you must fully qualify the Java datatype parameters.

2.3.1.3 Calling Published Stored Procedures

After publishing the stored procedure to SQL, you call it by using a SQL DML statement. For example, assume that this class is stored in the database:

public class Formatter {

   public static String formatEmp (String empName, String jobTitle) {

      empName = empName.substring(0,1).toUpperCase() + 

         empName.substring(1).toLowerCase();

      jobTitle = jobTitle.toLowerCase();

      if (jobTitle.equals("analyst"))

         return (new String(empName + " is an exempt analyst"));

      else

      return (new String(empName + " is a non-exempt " + jobTitle));

  }

}

Class Formatter has one method named formatEmp, which returns a formatted string containing an employee's name and job status. Create a call spec for Formatter as follows:

CREATE OR REPLACE FUNCTION format_emp (ename VARCHAR2, job VARCHAR2)

   RETURN VARCHAR2 

   AS LANGUAGE JAVA

   NAME 'Formatter.formatEmp (java.lang.String, java.lang.String)

   return java.lang.String';

   /

The call spec publishes the method formatEmp as format_emp. Invoke it as follows:

SELECT FORMAT_EMP(ENAME, JOB) AS "Employees" FROM EMP

   WHERE JOB NOT IN ('MANAGER', 'PRESIDENT') ORDER BY ENAME;

This statement produces the following output:

Employees

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

Adams is a non-exempt clerk

Allen is a non-exempt salesman

Ford is an exempt analyst

James is a non-exempt clerk

Martin is a non-exempt salesman

Miller is a non-exempt clerk

Scott is an exempt analyst

Smith is a non-exempt clerk

Turner is a non-exempt salesman

Ward is a non-exempt salesman

2.3.1.4 Dropping Published Stored Procedures

To remove classes from Oracle Lite, use either of the following:

• the dropjava utility

• the SQL DROP JAVA statement

To drop call specifications, use either DROP FUNCTION or DROP PROCEDURE.

dropjava {-user | -u} username/password[@database]

  [-option] filename filename ...

DROP JAVA { CLASS | RESOURCE } [<schema-name> .]<object_name>

DROP { FUNCTION | PROCEDURE } [<schema-name>.]<object_name>

2.3.1.5 Example

The following example creates a Java stored procedure using the load and publish model.

In this example, you store the Java method paySalary in the Oracle Lite database. paySalary computes the take-home salary for an employee. Oracle Lite automatically passes the current connection object to paySalary.

This example covers the following steps:

• Step 1: Create the Java Class

• Step 2: Load the Java Class into the Database

• Step 3: Publish the Function

• Step 4: Execute the Function

More examples of Java stored procedures are located in the Oracle_Home\Lite\Examples\Java directory.

Step 1: Create the Java Class

Create the Java class Employee in the file Employee.java. The Employee class implements the paySalary method:

import java.sql.*;

public class Employee {

   public static String paySalary(float sal, float fica, float sttax, 

                            float ss_pct, float espp_pct)  {

      float deduct_pct;

      float net_sal;

        // compute take-home salary 

      deduct_pct = fica + sttax + ss_pct + espp_pct;

      net_sal = sal * deduct_pct;

      String returnstmt = "Net salary is " + net_sal;

      return returnstmt;

   } // paySalary

}        

Note: The keyword Òpublic classÓ should not be used in a comment before the first public class statement.

Step 2: Load the Java Class into the Database

From SQL*Plus, load the Java class using CREATE JAVA, as follows:

CREATE JAVA CLASS USING BFILE ('c:\myprojects\doc', 

'Employee.class');

This command loads the Java class located in c:\myprojects\doc into the Oracle Lite database.

Step 3: Publish the Function

Create a call spec for the paySalary method. The following call spec publishes the Java method paySalary as function pay_salary:

CREATE FUNCTION pay_salary (

sal float, fica float, sttax float, ss_pct float, espp_pct float)

RETURN VARCHAR2

AS LANGUAGE JAVA NAME 

'Employee.paySalary(float, float, float, float, float)

return java.lang.String';

/

Step 4: Execute the Function

To execute pay_salary in SQL*Plus:

SELECT pay_salary(6000.00, 0.2, 0.0565, 0.0606, 0.1)

FROM DUAL;

To execute pay_salary in ODBC:

SQLExecDirect(hstm, 

   "SELECT pay_salary(6000.00,0.2,0.0565,0.0606,0.1)

   FROM DUAL);

Because the arguments to pay_salary are constants, the FROM clause specifies the dummy table DUAL. This SELECT statement produces the following output:

Net salary is 2502.6

2.3.2.1 Table-Level Stored Procedures

Table-level stored procedures are the static methods of the attached Java class. Therefore, when executing the method, Oracle Lite does not instantiate the class to which it belongs. In a call statement, you refer to table-level stored procedures as table_name.method_name.

Statement-level triggers and BEFORE INSERT and AFTER DELETE row-level triggers (see section ""Statement-Level vs. Row-Level Triggers"") must be table-level stored procedures.

2.3.2.2 Row-Level Stored Procedures

Row-level stored procedures are the non-static methods in the attached Java class. To execute a row-level stored procedure, Oracle Lite instantiates the class to which the procedure belongs. The arguments to the class constructor determine which column values the constructor uses as parameters to create the class instances. In a call statement, you refer to row-level stored procedures as method_name (without the table qualifier). Row-level triggers can indirectly execute row-level stored procedures.

2.3.2.3 Attaching a Java Class to a Table

To attach a Java class to a table, use the SQL command ALTER TABLE. The ALTER TABLE command has the following syntax:

ALTER TABLE [schema.]table 

   ATTACH JAVA {CLASS|SOURCE} "cls_or_src_name "

   IN {DATABASE|'cls_or_src_path '}

   [WITH CONSTRUCTOR ARGS (col_name_list )]

You can attach either a source file or a class file. Source files are compiled by the Java compiler found in the system path.

cls_or_src_name specifies a fully qualified name of a class or source file. This includes the package name followed by class name, such as Oracle.lite.Customer. Do not include the file extension in the class or source file name. The name is case-sensitive. If you use lowercase letters, enclose the name in double quotes (" "). Make sure that the source or class is in the package specified by cls_or_src_name. (The source file of the example class Customer should contain the line "package Oracle.lite;".) The class file is stored in the database in the same package. Oracle Lite creates the package if it does not already exist.

If you have already attached the Java class to another table in the database, you can use the IN DATABASE clause. If the class has not yet been attached, specify the directory location of the class or source file in cls_or_src_path.

Prior to executing a row-level stored procedure, Oracle Lite creates a Java object for the row, if one does not already exist. If the ALTER TABLE statement includes a WITH CONSTRUCTOR clause, Oracle Lite creates the object using the class constructor that is the best match given the datatypes of the columns included in col_name_list. If the ALTER TABLE statement does not include a WITH CONSTRUCTOR clause, Oracle Lite uses the default constructor.

You can use the ODBC functions SQLProcedures and SQLProcedureColumns to retrieve information about methods defined in a table.

Note the following regarding the ALTER TABLE statement:

• When the ALTER TABLE statement specifies arguments, Oracle Lite creates an instance of the class with the values passed in the argument list. If the Java application subsequently changes the values of those parameters, the new values are not automatically posted to the database. The application must use JDBC calls to update the database with the new values.

• If the ALTER TABLE statement does not specify a constructor and a row-level trigger is fired, Oracle Lite uses the default constructor to create the Java object.

2.3.2.4 Calling Attached Stored Procedures

After attaching the class to a table using the ALTER TABLE statement, you can call it with a SELECT statement. Refer to table-level stored procedures as table_name.method_name and row-level procedures as method_name.

For example, to execute a table-level stored procedure:

SELECT table_name.proc_name[arg_list]

   FROM {DUAL|[schema.]table WHERE condition};

The proc_name is the name of the table-level stored procedure. Each argument in arg_list is either a constant or a reference to a column in the table. If all the arguments of arg_list are constants, the FROM clause should reference the dummy table DUAL.

Execute a row-level stored procedure as follows:

SELECT [schema.]proc_name[arg_list]

   FROM [schema.]table

   WHERE condition;

If you call a procedure in the form table_name.method_name, and a table or method with that name does not exist, Oracle Lite assumes that table_name refers to a schema name and method_name refers to a procedure name. If you reference method_name only, Oracle Lite assumes that the referenced method is a row-level procedure. If there is no such procedure defined, however, Oracle Lite assumes that method_name refers to a procedure in the current schema.

2.3.2.5 Dropping Attached Stored Procedures

You use the ALTER TABLE command to drop stored procedures. ALTER TABLE has the following syntax:

ALTER TABLE [schema.]table 

   DETACH [AND DELETE] JAVA CLASS "class_name"

Detaching the Java class does not delete it from the database. To delete the Java class file from the database, use the DETACH AND DELETE statement.

If you delete a Java class from the database after invoking it as a stored procedure or trigger, the class remains in the Java Virtual Machine attached to the application. To unload the class from the Java Virtual Machine, commit changes to the database, if necessary, and close all applications connected to the database. To replace a Java class, you must close all connections to the database and reload the class.

2.3.2.6 Example

The following example shows how to create a Java stored procedure in Oracle Lite. In this example, you attach the Java method paySalary to the table EMP. paySalary computes the take-home salary for an employee. Oracle Lite passes the current connection object to paySalary as its first argument.

This example covers the following steps:

• Step 1: Create the Table

• Step 2: Create the Java Class

• Step 3: Attach the Java Class to the Table

• Step 4: Execute the Method

Step 1: Create the Table

Create the table using the following SQL command:

CREATE TABLE EMP(Col1 char(10));

Step 2: Create the Java Class

Create the Java class Employee in the file Employee.java. The Employee class implements the paySalary method:

import java.sql.*;

public class Employee {

   public static String paySalary(float sal, float fica, float sttax, 

                            float ss_pct, float espp_pct)  {

      float deduct_pct;

      float net_sal;

        // compute take-home salary 

      deduct_pct = fica + sttax + ss_pct + espp_pct;

      net_sal = sal * deduct_pct;

      String returnstmt = "Net salary is " + net_sal;

      return returnstmt;

   } // paySalary

}

Step 3: Attach the Java Class to the Table

From SQL*Plus, attach the Java class using the ALTER TABLE command:

ALTER TABLE EMP ATTACH JAVA SOURCE "Employee" IN 'C:\tmp';

This command attaches the Java source file for the Employee class, which resides in the directory C:\tmp, to the EMP table.

Step 4: Execute the Method

To execute the paySalary method in SQL*Plus, type the following statement:

SELECT EMP."paySalary"(6000.00,0.2,0.0565,0.0606,0.1)

   FROM DUAL;

To execute paySalary from ODBC, invoke SQLExecDirect:

SQLExecDirect(hstm, 

   "SELECT EMP.\"paySalary\"(6000.00,0.2,0.0565,0.0606,0.1)

   FROM DUAL);

This statement produces the following result:

Net salary is 2502.6

2.4.2.1 Returning Multiple Rows in ODBC

To execute a stored procedure that returns multiple rows in an OBDC application, use the following CALL statement, in which P is the name of the stored procedure and a₁ through a_n are arguments to the stored procedure.

{CALL P(a1,...,an)}

You use a marker (?) for any argument that should be bound to a value before the statement executes. When the statement executes, the procedure runs and the cursor on the result set is stored in the statement handle. Subsequent fetches using this statement handle return the rows from the procedure.

After you execute the CALL statement, use SQLNumResultCols to find the number of columns in each row of the result. Use the SQLDescribeCol function to return the column name and datatype.

2.4.2.2 Example

The following example shows how to use ODBC to execute a stored procedure that returns multiple rows. This example does not use the SQLNumResultCols or SQLDescribeCol functions. It assumes that you have created a stored procedure, which you have published to SQL as PROC. PROC takes an integer as an argument.

rc = SQLPrepare(StmtHdl, "{call PROC(?)}", SQL_NTS);

CHECK_STMT_ERR(StmtHdl, rc, "SQLPrepare");


rc = SQLBindParameter(StmtHdl, 1, SQL_PARAM_INPUT_OUTPUT,

   SQL_C_LONG,SQL_INTEGER, 0, 0, &InOutNum, 0, NULL);

CHECK_STMT_ERR(StmtHdl, rc, "SQLBindParameter");


rc = SQLExecute(StmtHdl);

CHECK_STMT_ERR(StmtHdl, rc, "SQLExecute");


/* you can use SQLNumResultCols and SQLDescribeCol here */


rc = SQLBindCol(StmtHdl, 1, SQL_C_CHAR, c1, 20, &pcbValue1);

CHECK_STMT_ERR(StmtHdl, rc, "SQLBindCol");


rc = SQLBindCol(StmtHdl, 2, SQL_C_CHAR, c2, 20, &pcbValue2);

CHECK_STMT_ERR(StmtHdl, rc, "SQLBindCol");


while ((rc = SQLFetch(StmtHdl)) != SQL_NO_DATA_FOUND) {

   CHECK_STMT_ERR(StmtHdl, rc, "SQLFetch");

   printf("%s, %s\n", c1, c2);

}

2.5.2.1 Enabling and Disabling Triggers

When you create a trigger, it is automatically enabled. To disable triggers, use the ALTER TABLE or ALTER TRIGGER statement.

To enable or disable individual triggers, use the ALTER TRIGGER statement, which has the following syntax:

ALTER TRIGGER <trigger_name> {ENABLE | DISABLE}

To enable or disable all triggers attached to a table, use ALTER TABLE:

ALTER TABLE <table_name> {ENABLE | DISABLE} ALL TRIGGERS