Oracle Database Triggers / Java Stored Procedures

From: Frederick G. Burkley <fburkley_at_home.com>
Date: Sat, 21 Jul 2001 23:51:50 GMT
Message-ID: <3B22FF08.D80226F2@home.com>

Hello,

I am very new to Oracle so do not have a lot of background here... I have Oracle 8.1.6 Personal Edition on NT 4, and am using XML tools from XDK for Java 8.1.7.1.0A.

I want to set up a Java Stored Procedure and have it fire off of a database trigger. The stored procedure will call a Java class that I load into the database.

The Java class will essentially do a "SELECT * FROM SCOTT.BLAHBLAH" and send a XML stream off to another server for processing.

The database trigger will fire after an insert or update or delete on the table SCOTT.BLAHBLAH.

The sequence of events is:

1) Someone inserts, deletes of modifies a row in SCOTT.BLAHBLAH
2) The trigger fires and calls the Java Stored Procedure
3) The stored procedure calls a static method on a Java class that

SELECTs rows from SCOTT.BLAHBLAH and sends them off to another server. My Java class/static method builds the SQL and passes the SQL string to OracleXMLQuery (conn, sql.toString ());

I include 3 "source" items at the end of this message: 1) OracleInternalXMLQuery.java - Does the SELECT and sends the result (as an XML string) offboard for processing. 2) My Oracle PL/SQL procedure - TestOracleInternalXmlQuery 3) My Oracle PL/SQL trigger - blahblah_after_update_row

I have two questions:

1. My first trigger was a row level trigger and I ran into the
   "ORA-04091 table name is mutating, trigger/function may not see it"
   problem. So I change my trigger (just removed the "for each row") to a statement level trigger and it works much better.

I am looking for resources on *how* to write database triggers. I am looking for books on the topic or on line resources...

2) The thing I am really after is a trigger / Java Stored Procedure that will just stream off the *rows that changed* from the table SCOTT.BLAHBLAH. You know, maybe I have a large number of rows and I just want to send off the rows that were affected by an update, delete or insert.

I will appreciate any thoughts on this, or pointers in the right direction.

Thank you,
Fred Burkley

---

Java class OracleInternalXMLQuery

---

import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.xml.sql.query.OracleXMLQuery;

/**
 *
 */

public class OracleInternalXmlQuery {

  /** */
  static Connection conn = null;
  static String default8iConn = "jdbc:oracle:kprb:";   static String thinConn =
"jdbc:oracle:thin:@MY_HOST_NAME:1521:MY_SID";

  // static initializers
  static {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");     }
    catch (ClassNotFoundException cnfe) {       cnfe.printStackTrace ();
    }

    try {

      if (insideOracle8i ()) {
        conn = DriverManager.getConnection(default8iConn, "SCOTT",


"TIGER");

      }
      else {
        conn = DriverManager.getConnection(thinConn, "SCOTT", "TIGER");
      }

    }
    catch (SQLException sqle) {
      sqle.printStackTrace ();
    }
  }

  /**
   *
   */

  public static void runXmlQuery () {

    StringBuffer sql = new StringBuffer ("SELECT * FROM BLAHBLAH");     OracleXMLQuery xmlQuery = new OracleXMLQuery (conn, sql.toString ());

    String xmlString = xmlQuery.getXMLString ();     System.out.println (xmlString);

    // write to socket
    try {

      Socket soc = new Socket ("MY_HOST_NAME", 8005);
      PrintWriter out = new PrintWriter (soc.getOutputStream ());
      out.print (xmlString);
      out.close ();

    }
    catch (Exception ex) {
      ex.printStackTrace ();
    }
    finally {
      try {fw.close ();} catch (Exception ignore) {}     }
  }

  /**
   *
   */

  public static boolean insideOracle8i () {     boolean ret = false;
    String version = System.getProperty ("oracle.server.version");     if (version != null && !version.equals ("")) {       ret = true;
    }
 return ret;
  }

  /**
   *
   */

  public static void main (String[] args) {     runXmlQuery ();
  }
}

---

Oracle PL/SQL procedure -- this is what calls the static method on my Java class !!

---

create or replace procedure TestOracleInternalXmlQuery as
language JAVA
name 'OracleInternalXmlQuery.runXmlQuery()'; /

---

Oracle PL/SQL trigger

---

create or replace trigger blahblah_after_update_row after insert or update or delete
on SCOTT.BLAHBLAH
begin
  TestOracleInternalXmlQuery;
end;
/

--

Received on Sat Jul 21 2001 - 18:51:50 CDT