Calling Java stored procedures from triggers

Coupling triggers and Java-stored procedures can facilitate constraint or application requirements.

Triggers are often employed to handle pre- and post-data manipulation language (DML) processing and can be used to facilitate complex constraint requirements. Oracle supports the use of Java in the database as a non-proprietary, powerful programming language. Coupling triggers and Java stored procedures can satisfy platform-independent database programming policies and often complicated application-related DML processing requiremen...

ts.

The example below contains a trigger that utilizes a call to a Java stored procedure to enforce a primary key constraint. The sample is a simplified example of how Java stored procedures can be employed by triggers to facilitate constraint or application requirements. An insert before trigger calls the Java stored procedure to verify the record being inserted is unique. The Java stored procedure returns the verification result. If the record already exists, the trigger raises and outputs the exception.

Included in the example is the Java program source, the DDL to create and populate a customer table, the DDL to create the call spec required to access the class, and the DDL to create the trigger. Also included is a UNIX korn shell script that can be used to load the class into the database and call the DDL script.

An Oracle 8.1.7 database running on Solaris 2.6 was used for the example but it should work in any Oracle 8i-version database running on Unix or NT platforms.

Steps:

  1. Compile CustTr.java
  2. Execute the deploy_jproc_samp.ksh script:
    deploy_jproc_samp.ksh user/password 
    
  3. Test the example by selecting the call spec function from dual:
    Select jproc.InsFun(1,?Test?) from dual; 
    
    You can also test the trigger by inserting a duplicate record:
    Insert into customer values(1,?Test?); 
    

This is the CustTr.java source:

/* Class CustTr contains method InsFun that tests the uniqueness 
   of an inserted record. */ 

import java.sql.*; 
import java.io.*; 
import oracle.jdbc.*; 

public class CustTr { 

 public static int InsFun(int cust, String org) 
                            throws SQLException { 

       Connection conn = DriverManager.getConnection("jdbc:default:connection:"); 
       int numrows = 0; 
  int numret = 0; 
       if ((cust != 0) && (org != null)){ 

       String sql = "SELECT COUNT(*) FROM CUSTOMER WHERE CUSTID = " + cust; 

          try { 
           /*      PreparedStatement pstmt = conn.createStatement(); */ 
     PreparedStatement pstmt = conn.prepareStatement(sql); 
                   ResultSet rset = pstmt.executeQuery(sql); 

                   while (rset.next()) {numrows = rset.getInt(1);} 

                   if (numrows != 0) //Test Failed 
      numret = 1; 
        else {// Query Passed 
      numret = 0; 
        } 

                        pstmt.close(); 
                        rset.close(); 
      conn.close(); 

          } catch (SQLException e) {System.err.println(e.getMessage());} 

          }//End of If 
      return numret; 

   } 
} 

Next, here is the cr_jrpoc_samp.sql script that contains the required DDL:

-- cr_jproc_samp.sql 
-- This script creates the customer table, INSFUN call spec, and CUST_TR 
-- trigger used by the trigger/java-stored procedure example. 

CREATE TABLE CUSTOMER 
(CUSTID NUMBER(10) NOT NULL, 
 ORGNAME VARCHAR2(45) NOT NULL); 
  
insert into customer values (1, 'Test Organization Name'); 
commit; 

CREATE OR REPLACE  PACKAGE JPROC  
AUTHID CURRENT_USER AS 
FUNCTION INSFUN (cust IN NUMBER, org IN VARCHAR2) 
 RETURN NUMBER 
AS LANGUAGE JAVA 
NAME 'CustTr.InsFun(int, java.lang.String) return int'; 
END JPROC; 
/ 

CREATE OR REPLACE TRIGGER CUST_TR BEFORE 
    INSERT 
    ON CUSTOMER 
    FOR EACH ROW 
    Declare numrows integer; 

Begin 

select jproc.insfun(:new.custid,:new.orgname) 

into numrows from dual; 
  if numrows > 0 
  then 
  raise_application_error(-20001, 'Database Integrity Violation - 
  Cannot INSERT row in table CUSTOMER because '||:new.custid||' 
  already exists!'); 
  end if; 
    
end; 
/ 

The korn shell script below can be used to automate the creation and deployment of the sample by loading the class and calling the cr_jproc_samp.sql script:

# 
# deploy_jproc_samp.ksh 
# 
# Purpose: 
# This script deploys the sample java stored procedure, 
# creates and populates the customer table, and creates the 
# jproc package and call spec function for the cust_trig stored 
# procedure. 
# 
#  Author:      Jeff Tarnok 
#  Date:        Aug 23 2001 
#  Version History: 
#  Version 1.00.00 
#
#  USAGE="Usage: deploy_jproc_samp.ksh {user/password}" 

For More Information


This was first published in September 2001

Dig deeper on Oracle stored procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close