Re: Oracle Database Triggers / Java Stored Procedures

In pl/sql you have access to the :new.fieldName which is the value coming in and the :old.fieldName the current value and you know if it is inserting, deleting, updating and so you don't have to do the select from scott.blahblah in java. Instead pass in the information that you want to the java class and have it do the xml etc. manipulation and send it where ever you wanted to. You would be using a for each row trigger. I would assume an after trigger incase you have another trigger for some data validation.(a before trigger; which if the data did not pass some business rule you would raise an error. thus you want your "copy" routine to only fire after it has passed all before triggers.)

Jim

"Frederick G. Burkley" <fburkley_at_home.com> wrote in message news:3B22FF08.D80226F2_at_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:55 CDT