Re: Issue involving Trigger , PL/SQL Procedure & a Java stored procedure
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Feb 2004 09:28:06 -0500
That is because triggers don't see the current version of the table. Had
you tried with PL/SQL, you'd have recieved "table mutants" or "mutating table"
error. Interesting that Java lets you circumvent the error. Is is a feature
or a "feature"?
On 02/25/2004 08:56:24 AM, ramalingam.rangadoure@xxxxxxx wrote:
> 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.
>
> Please let me know if anybody has a solution for this issue.
>
>
>
> Thanks and Regards,
> R. Rangadoure
> ************** 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("jdbc:oracle: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!");
>
> }
>
> }
>
>
>
>
>
>
> DISCLAIMER: The information contained in this message is intended only and
> solely for the addressed individual or entity indicated in this message and
> for the exclusive use of the said addressed individual or entity indicated in
> this message (or responsible for delivery
> of the message to such person) and may contain legally privileged and
> confidential information belonging to Tata Consultancy Services. It must not
> be printed, read, copied, disclosed, forwarded, distributed or used (in
> whatsoever manner) by any person other than the
> addressee. Unauthorized use, disclosure or copying is strictly prohibited and
> may constitute unlawful act and can possibly attract legal action, civil
> and/or criminal. The contents of this message need not necessarily reflect or
> endorse the views of Tata Consultancy Services
> on any subject matter. Any action taken or omitted to be taken based on this
> message is entirely at your risk and neither the originator of this message
> nor Tata Consultancy Services takes any responsibility or liability towards
> the same. Opinions, conclusions and any other
> information contained in this message that do not relate to the official
> business of Tata Consultancy Services shall be understood as neither given
> nor endorsed by Tata Consultancy Services or any affiliate of Tata
> Consultancy Services. If you have received this message in error,
> you should destroy this message and may please notify the sender by e-mail.
> Thank you.
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
