MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Java Trigger Sample

with 5 comments

Database triggers in Oracle 10g or 11g (and all predecessors) are really PL/SQL components. You’ll find that the Oracle SQL manual points you to the Oracle PL/SQL manual when discussing database triggers. A post in the forum and question from some PL/SQL students prompted me to write this post. I hope it helps a few others too.

This demonstrates how to put the logic of database triggers into Java libraries in the database. Before you write any database trigger, you first plan what you want to accomplish. The idea for this sample is one that I originally put in the Oracle Database 11g PL/SQL Programming book (assuaging the publisher). It is quite simple, there should only be a maximum of two signers on a video store account to prevent children from renting materials disallowed by their parents.

This looks at how you implement a Java library inside a database trigger. Two approaches toward the trigger are shown. The first raises an unhandled Java exception, and the second simply returns a 0 for false and a 1 for true.

Both examples create a Java library, a PL/SQL stored program unit, and a database trigger. All examples were done in an Oracle 11g database.

The first example might be how some Java programmers would choose to implement a program. It implements a method that throws an unhandled exception. The expectation is that the calling program unit must implement a try-catch block when calling the method. Unfortunately, that’s not the case when the calling unit is a PL/SQL wrapper (either stored function or procedure).

1. An unhandled exception model:

You create the Java source, or static library method as shown. A class may contain more than one method, but you must provide a PL/SQL wrapper to each method when you want to use them inside the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ThrowAnError" AS
 
  // Required class libraries.
  import java.sql.*;
  import oracle.jdbc.driver.*;
 
  // Define class.
  public class ThrowAnError {
 
    // Connect and verify new insert would be a duplicate.
    public static void contactTrigger(Integer memberID) throws SQLException {
      Boolean duplicateFound = false;
 
      // Create a Java 5 and Oracle 11g connection.
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Create a prepared statement that accepts binding a number.
      PreparedStatement ps = conn.prepareStatement("SELECT   null " +
                                                   "FROM     contact c JOIN member m " +
                                                   "ON       c.member_id = m.member_id " +
                                                   "WHERE    c.member_id = ? " +
                                                   "HAVING   COUNT(*) > 1");
 
      // Bind the local variable to the statement placeholder.
      ps.setInt(1, memberID);
 
      // Execute query and check if there is a second value.
      ResultSet rs = ps.executeQuery();
      if (rs.next())
        duplicateFound = true;
 
      // Clean up resources.
      rs.close();
      ps.close();
      conn.close();
 
      // Throw exceptions when two signers already exist.
      if (duplicateFound)
        throw new SQLException("Too many contacts per account."); }}
/

While deprecated in Oracle 11g, please note that Oracle 10g requires a different syntax to create a connection. It follows:

  // Create an Oracle 10g JDBC connection.
  Connection conn = new OracleDriver().defaultConnection();

You create a PL/SQL stored procedure as a wrapper to a Java method that returns a void data type.

CREATE OR REPLACE PROCEDURE java_contact_t4 (member_id_in NUMBER) IS
LANGUAGE JAVA
NAME 'ThrowAnError.contactTrigger(java.lang.Integer)';
/

After you create the wrapper, you can create a trigger that calls the stored procedure. Here’s an example of such a trigger:

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_t4 
BEFORE INSERT ON contact
FOR EACH ROW
WHEN (NEW.member_id IS NOT NULL)
BEGIN
    java_contact_t4 (:NEW.member_id);
END;
/

When you test the trigger by inserting a third contact signature, you’ll raise the following exception:

SQL> INSERT INTO contact VALUES
  2  ( 1008
  3  , 1002
  4  , 1003
  5  , 'Schmeaux'
  6  , 'Joe'
  7  , ''
  8  , 2, SYSDATE, 2, SYSDATE)
  9  /
INSERT INTO contact VALUES
            *
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
java.sql.SQLException: Too many contacts per account.
ORA-06512: at "PLSQL.JAVA_CONTACT_T4", line 1
ORA-06512: at "PLSQL.CONTACT_T4", line 2
ORA-04088: error during execution OF TRIGGER 'PLSQL.CONTACT_T4'

2. A no exception model:

Much like the one earlier, you create the Java source, or static library method as shown. While classes stored internally in the database may contain more than one method, you must provide a PL/SQL wrapper for each. This class contains only one method and wrapper.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ThrowAnError" AS
 
  // Required class libraries.
  import java.sql.*;
  import oracle.jdbc.driver.*;
 
  // Define class.
  public class ThrowAnError {
 
    // Connect and verify new insert would be a duplicate.
    public static int contactTrigger(Integer memberID) throws SQLException {
      Boolean duplicateFound = false;
 
      // Create a Java 5 and Oracle 11g connection.
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Create a prepared statement that accepts binding a number.
      PreparedStatement ps = conn.prepareStatement("SELECT   null " +
                                                   "FROM     contact c JOIN member m " +
                                                   "ON       c.member_id = m.member_id " +
                                                   "WHERE    c.member_id = ? " +
                                                   "HAVING   COUNT(*) > 1");
 
      // Bind the local variable to the statement placeholder.
      ps.setInt(1, memberID);
 
      // Execute query and check if there is a second value.
      ResultSet rs = ps.executeQuery();
      if (rs.next())
        duplicateFound = true;
 
      // Clean up resources.
      rs.close();
      ps.close();
      conn.close();
 
      // Return 1 for true when two signers already exist and 0 when they don't.
      if (duplicateFound)
        return 1;
      else
        return 0; }}
/

You create a PL/SQL stored function as a wrapper to a Java method that returns an int data type. It returns a 1 when true and a 0 when false.

CREATE OR REPLACE FUNCTION java_contact_t4 (member_id_in NUMBER) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'ThrowAnError.contactTrigger(java.lang.Integer) return int';
/

When you write the trigger, you include the natural exception there. That’s where it belongs! Perhaps, it is another example of why PL/SQL skills are so important in Oracle technology. The RAISE_APPLICATION_ERROR function exists for you to define end-user error handling and custom messages.

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE TRIGGER contact_t4 
BEFORE INSERT ON contact
FOR EACH ROW
WHEN (NEW.member_id IS NOT NULL)
BEGIN
  IF java_contact_t4 (:NEW.member_id) = 1 THEN
    RAISE_APPLICATION_ERROR(-20001,'Already two signers');
  END IF;
END;
/

You test it the same way as before. However, when you attempt to insert a third contact signature, you’ll raise the following exception:

SQL> INSERT INTO contact VALUES
  2  ( 1008
  3  , 1002
  4  , 1003
  5  , 'Schmeaux'
  6  , 'Joe'
  7  , ''
  8  , 2, SYSDATE, 2, SYSDATE)
  9  /
INSERT INTO contact VALUES
            *
ERROR at line 1:
ORA-20001: Already two signers
ORA-06512: at "PLSQL.CONTACT_T4", line 3
ORA-04088: error during execution OF TRIGGER 'PLSQL.CONTACT_T4'

You probably notice that the second one is much cleaner because it doesn’t raise an ORA-29532 or Java call terminated by uncaught Java exception error. Somehow any exception message that says it is unhandled looks a bit unprofessional to me. That’s why I’d recommend the second approach.

Help is on the horizon. Oracle released a new class to manage these, and map them back to errors. You can find it in the Fusion middleware for Oracle 11g. At some future date Oracle may include the oracle.dss.util.TypeNotSupportedException inside the database, which is currently delivered in the Fusion middleware stack.

Written by maclochlainn

November 27th, 2008 at 9:19 pm

Posted in Uncategorized

5 Responses to 'Java Trigger Sample'

Subscribe to comments with RSS or TrackBack to 'Java Trigger Sample'.

  1. […] da tag java, OOP, oracle, PL, SQL Un esempio di creazione di un trigger in un DB Oracle, utilizzando il linguaggio Java direttamente dentro il […]

    Oracle & Java « Alessioma’s Weblog

    7 May 09 at 10:37 am

  2. Thanks for the nice tutorial on java triggers.

    Sandeep

    18 Mar 13 at 4:40 am

  3. I searched such example for a whole day and finally found such a nice explanation with examples on your blog! Awesome stuff, keep doing! Thanks a lot.

    Gaurang

    29 Aug 13 at 1:42 am

  4. Great information… Thanks a lot

    Eric Chiang

    2 Nov 16 at 8:12 pm

  5. Thanks

    SONPAL

    12 Feb 18 at 5:13 am

Leave a Reply