Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    22

    Red face Unanswered: Issue involving Trigger , PL/SQL Procedure & a Java stored procedure

    Hi all,

    Scenario:

    We have an issue with PL/SQL Trigger involving another PL/SQL Procedure & a Java stored procedure.

    The trigger will be invoked when ever there is any transaction occurs on a particular table. This calls a PL/SQL Procedure that inturn invokes a Java stored procedure.

    The Java stored procedure will reads the data from the table and write it to a text file.

    Issue:

    The issue we are facing is that the Java stored procedure is unable to get the latest changes made to the table, which invoked the trigger.
    Example:
    We have 4 records in the table. If we insert another record, then it inturns fires the trigger and that calls the procedure and Java stored procedure.
    The Java stored procedure was able to select only the 4 records but not the 5 record which has fired the trigger.

    The event of the trigger is mentioned as "after insert/delete/update for each row".
    Attached the code for your reference below.

    Please let me know if anybody has a solution for this issue.

    ************************************************** *

    ************** TRIGGER *******

    CREATE OR REPLACE TRIGGER "DEV1"."TR_JOBS" AFTER
    INSERT
    OR UPDATE
    OR DELETE OF "COMMENTS", "DAY_OF_MONTH", "DAY_OF_WEEK", "FREQUENCY", "HOUR", "JOB_ID", "MINUTE", "MONTH_OF_YEAR", "SCRIPT_ID", "STATE", "TIME_SETTINGS", "USER_NAME" ON "DEV1"."T_DTJOBS" FOR EACH ROW sp_jobs;

    ******** PROCEDURE *************
    CREATE OR REPLACE PROCEDURE "DEV1"."SP_JOBS" AS LANGUAGE JAVA
    NAME 'BatchCreateCronJobFile.createCronJobsFile()';

    ******************** BatchCreateCronJobFile JAVA CLASS ***********8
    import java.io.*;
    import java.util.Vector;
    import java.util.Calendar;
    import java.sql.*;

    /**
    *
    * Description : This class is triggered after t_dtjobs table is modified.
    * This class gets the active jobs data from DB and writes it into the cronJobs File
    * @Class name : BatchCreateCronJobFile
    * @Version : 1.0
    * @Author : Tata Consultancy Services
    * Creation date : 21/02/2004
    * Maintenance History
    * Change Request Ref Date Modified Modified by Brief Description
    *
    */

    public class BatchCreateCronJobFile
    {
    /**

    * Description : Gets the active jobs data from DB and writes it into the cronJobs File

    */
    public static boolean createCronJobsFile()
    {
    BufferedWriter l_bufferedWriter = null;
    String record = null;
    String s_fileNameWithPath = "c:\\test\\abc.txt";
    BatchDBConnection batchDBConnection = new BatchDBConnection();
    try
    {
    l_bufferedWriter = new BufferedWriter(new FileWriter(new File(s_fileNameWithPath)));
    ResultSet rset = batchDBConnection.executeQuery("Select time_settings||' java BatchProcessJob '||job_Id from t_dtJobs");
    while (rset.next())
    {
    l_bufferedWriter.write(rset.getString(1));
    l_bufferedWriter.newLine();
    }
    l_bufferedWriter.flush();
    Statement stmt = rset.getStatement();
    stmt.close();
    }
    catch(Exception e)
    {
    System.out.println("Exception is "+e);
    return false;
    }
    return true;
    }

    /**
    * Description : Default Constructor
    */

    public BatchCreateCronJobFile()
    {
    }
    public static void main (String args[])
    {
    BatchCreateCronJobFile batchCreateCronJobFile = new BatchCreateCronJobFile();
    boolean status = batchCreateCronJobFile.createCronJobsFile();
    }
    }

    ********************* BatchDBConnection *************8

    import java.io.PrintStream;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.HashMap;
    import javax.naming.InitialContext;

    /**

    *

    * Description : This is a Database connection class used for getting the connection

    * @Class name : BatchDBConnection

    * @Version : 1.0

    * @Author : Tata Consultancy Services

    * Creation date : 20/02/2004

    * Maintenance History

    * Change Request Ref Date Modified Modified by Brief Description

    *

    */



    class BatchDBConnection

    {

    /**

    * Description : Creates and returns a connection to DB

    */



    public Connection getConnectionToDB()

    {

    Connection conn = null;

    try

    {

    String driverName = "oracle.jdbc.driver.OracleDriver";

    Class.forName(driverName);



    conn = DriverManager.getConnection("jdbcracle:thin:@172.20.134.16:1521:1521", "dev1", "dev1");

    }

    catch(Exception e)

    {

    System.out.println("Exception is "+e);

    }

    return conn;

    }



    /**

    * Description : Executes a select query and return the ResultSet

    */



    public ResultSet executeQuery(String query) throws Exception

    {

    Connection conn = getConnectionToDB();

    Statement stmt = conn.createStatement();

    ResultSet rset = stmt.executeQuery(query);

    return rset;

    }



    /**

    * Description : Executes an insert/update/delete query and return no of rows affected

    */



    public int executeUpdate(String query) throws Exception

    {

    Connection conn = getConnectionToDB();

    Statement stmt = conn.createStatement();

    int returnValue = stmt.executeUpdate(query);

    return returnValue;

    }



    public static void main(String[] args)

    {

    System.out.println("Hello World!");



    int noOfRowsUpdated=0;

    int noOfColumns = 0;

    int iter=0;

    BatchDBConnection batchDBConnection = new BatchDBConnection();



    try

    {

    ResultSet rset = batchDBConnection.executeQuery("select * from t_dtJobs");

    ResultSetMetaData rsmd = rset.getMetaData();

    noOfColumns = rsmd.getColumnCount();



    for(iter=1;iter<=noOfColumns;iter++)

    {

    System.out.print(rsmd.getColumnName(iter)+" "); // Print col 1

    }

    System.out.println();



    while (rset.next())

    {

    for(iter=1;iter<=noOfColumns;iter++)

    {

    System.out.print(rset.getString(iter)+" "); // Print col 1

    }

    System.out.println();

    }

    Statement stmt = rset.getStatement();

    stmt.close();

    }

    catch(Exception e)

    {

    System.out.println("Exception is "+e);

    }

    System.out.println("Ending!");

    }

    }

    *********************************************

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Issue involving Trigger , PL/SQL Procedure & a Java stored procedure

    You don't want to do that! Of course the Java procedure can't see the data, because it runs in a different session (new BatchDBConnection...) and you haven't commited in the first session yet. What's more, you may in fact roll back the triggering statement, in which case you don't want the data written to the file (I presume).

    I would suggest that in the trigger you call DBMS_JOB.SUBMIT to schedule a job to run the procedure. The job will only run once the triggering statement is committed, and will not run at all if it rolls back.

    Another advantage of this approach is that the session that fired the trigger won't have to wait however long your Java routine takes to write to file before continuing: the write to file will be asynchronous.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •