Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Issue involving Trigger , PL/SQL Procedure & a Java stored pr ocedure

RE: Issue involving Trigger , PL/SQL Procedure & a Java stored pr ocedure

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Wed, 25 Feb 2004 09:35:56 -0500
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5933@exchsen0a1ma>


R. Rangadoure,

You are not finding the new record because the Java program is establishing a new connection to the database - it is not a part of the current transaction. Since it is a new connection, and the insert has not been committed yet, it does not see the new data.

I think if you want this to work, you will need to pass the data to the Java program from the database trigger call. Either that, or find way to perform the commit and then call the Java program.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Mladen Gogala [mailto:mladen_at_wangtrading.com] Sent: Wednesday, February 25, 2004 9:28 AM To: oracle-l_at_freelists.org
Subject: Re: Issue involving Trigger , PL/SQL Procedure & a Java stored procedure

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_at_tcs.com 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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Feb 25 2004 - 08:35:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US