import java.io.*;
import java.sql.*;
import java.util.*;


/****** Proof Of Concept
  of an non-understandable "java.sql.SQLException: Value conflicts occurs" thru OJDBC driver

Summary :
=========
- In session1 (using JDBC), 5 records are retrieved with FOR UDPATE and usage of MIN() function
  on a column in the where clause, before being deleted.
- In session2 *DURING* the above processing, an SQL query is updating the column looked for min value
  with a value *LOWER* than the one actually existing.

==> "Value conflicts occurs" exception occurs once the seconds session has commited,
    during the next ResultSet.deleteRow() on the first session.



Based on http://docs.oracle.com/cd/B19306_01/java.102/b14355.pdf

Page 19-14 and 19-15
---------------------------------------------------------------------------------------------
It is important to be aware of the following facts regarding updatable result sets with the JDBC drivers:
- The drivers do not enforce write locks for an updatable result set.
- The drivers do not check for conflicts with a result set DELETE or UPDATE operation.
A conflict will occur if you try to perform a DELETE or UPDATE operation on a row
updated by another committed transaction.
The Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table.
As long as the ROWID is valid when a driver tries to send an UPDATE or DELETE
operation to the database, the operation will be run.
The driver will not report any changes made by another committed transaction. Any
conflicts are silently ignored and your changes will overwrite the previous changes.
To avoid such conflicts, use the Oracle FOR UPDATE feature when running the query
that produces the result set. This will avoid conflicts, but will also prevent
simultaneous access to the data. Only a single write lock can be held concurrently on a
data item.
---------------------------------------------------------------------------------------------

and

page 19-19 table 19-1
---------------------------------------------------------------------------------------------
Summary of Visibility of Internal and External Changes :
Table 19–1 summarizes the discussion in the preceding sections regarding whether a
result set object in the Oracle JDBC implementation can see changes made internally
through the result set itself, and changes made externally to the underlying database
from elsewhere in your transaction or from other committed transactions

                    Can See     Can See     Can See     Can See     Can See     Can See
                    Internal    Internal    Internal    External    External    External
Result Set Type     DELETE?     UPDATE?     INSERT?     DELETE?     UPDATE?     INSERT?
forward-only            no          yes         no          no          no          no
scroll-sensitive        yes         yes         no          no          yes         no
scroll-insensitive      yes         yes         no          no          no          no
---------------------------------------------------------------------------------------------


I would expect that once I the query was firstly executed and the ResultSet initially filled,
the min value was computed and didn't change (or get checked against DB) at deletion of one record
of the ResultSet, leading to "Value Conflict Occurs" exception.

The ResultSet in the code was FORWARD ONLY type.

The update is done on another row of the ones retrieved in the ResultSet, so I beg in not something
about locking, or ROWID
The actual query is input by end-user, and should retrieve records that he wanted to be processed.
So another approach (I mean solution) could not be to do the work in 2 steps :
   *FIRST* get the min value and *THEN* use that value in the where clause.


I've tried change the query to (hopefully) compute the min value only once :
eg:
SELECT B
FROM POC p, (select min(C) "MIN_C" from POC where A>100) MIN_POC
WHERE A>100 and C=MIN_POC.MIN_C
or
WITH MIN_POC AS (SELECT min(C) min_c FROM POC WHERE A>100)
SELECT B
FROM POC, MIN_POC
WHERE A>100 AND C=MIN_C

NOTE : I cannot set FOR UPDATE or I get this error:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

And during execution, I have this:
 java.sql.SQLException: Invalid operation for read only resultset: deleteRow


###################
###  TEST CASE  ###
###################


1 : CREATE TABLE
################
---------------------------------------------------------------------------------------------
CREATE TABLE POC
( A  NUMBER,
  B  NUMBER,
  C  NUMBER
);
---------------------------------------------------------------------------------------------


2 : FILL THE TABLE
##################
---------------------------------------------------------------------------------------------
-- insert 5 records with each same 'C' from which we will retrieve min from table
INSERT INTO POC VALUES (101, 1, 100);
INSERT INTO POC VALUES (102, 1, 100);
INSERT INTO POC VALUES (103, 1, 100);
INSERT INTO POC VALUES (104, 1, 100);
INSERT INTO POC VALUES (105, 1, 100);
-- and 1 record that will be updated to have a lower C than 100...
INSERT INTO POC VALUES (999, 9, 999);
---------------------------------------------------------------------------------------------

3 : CUSTOMIZE config file  (config.txt)
#######################################
---------------------------------------------------------------------------------------------
IP_DB:=demo10g
PORT_DB:=1521
SID:=L10G
USR_DB:=HF509
PWD_DB:=HF509
SQL_SELECT:=SELECT B FROM POC WHERE A>100 and C=(select min(C) from POC where A>100) FOR UPDATE
SQL_UPDATE:=UPDATE POC set C = MOD(A,100) WHERE  A>100 and C=(select max(C) from POC where A>100)
SQL_SELECT_OK:=SELECT B FROM POC WHERE A>100 and C=(select min(C) from POC where A>100) FOR UPDATE
SQL_SELECT_KO1:=SELECT B FROM POC p, (select min(C) "MIN_C" from POC where A>100) MIN_POC WHERE A>100 and C=MIN_POC.MIN_C
SQL_SELECT_KO2:=WITH min_poc AS (SELECT min(C) min_c FROM POC WHERE A>100) select B from POC, MIN_POC where A>100 and C = MIN_C
SQL_SELECT_KO3:=SELECT B FROM POC WHERE A>100 and C=POC_MIN.get_min_POC(0) FOR UPDATE
---------------------------------------------------------------------------------------------
Customize the first 5 variables to get connection to DB.
Note that 5 lasts variable are not used. It is just to have them available and quickly switch for testing.
(ie: the active query used in runtime will be the 'SQL_SELECT' one)


4 : launch jar file simulated GOLD JMS Agent  (windows .bat example)
############################################
---------------------------------------------------------------------------------------------
set CLASSPATH=.;E:\_WORK_\_OJDBC_\ojdbc14_20100407_10.2.0.5.jar
java runME config.txt
---------------------------------------------------------------------------------------------


5 : Wait for the first DELETE (5 seconds) and run the UPDATE + COMMIT in *ANOTHER* Oracle session
#################################################################################################
---------------------------------------------------------------------------------------------
UPDATE POC set C = MOD(A,100) WHERE  A>100 and C=(select max(C) from POC where A>100);
COMMIT;
---------------------------------------------------------------------------------------------


6 : OUTPUT EXAMPLE :
####################
---------------------------------------------------------------------------------------------
sql_SEL=SELECT B FROM POC WHERE A>100 and C=(select min(C) from POC where A>100) FOR UPDATE
OK
jdbc:oracle:thin:@demo10g:1521:L10G,HF509,HF509
con=oracle.jdbc.driver.T4CConnection@19fcc69
Record No0
Done ! It's time to run the UPDATE in another session and commit !
Record No1
ex=Un conflit de valeur s'est produit
java.sql.SQLException: Un conflit de valeur s'est produit
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209)
        at oracle.jdbc.driver.UpdatableResultSet.executeDeleteRow(UpdatableResultSet.java:2835)
        at oracle.jdbc.driver.UpdatableResultSet.deleteRow(UpdatableResultSet.java:1863)
        at runME.main(runME.java:235)
---------------------------------------------------------------------------------------------



7 : in case of need, here's how to compile this source.
#######################################################
---------------------------------------------------------------------------------------------
javac -cp E:\_WORK_\_OJDBC_\ojdbc14_20100407_10.2.0.5.jar;. runME.java
jar cf runME.jar *.class
---------------------------------------------------------------------------------------------


8 : Optionnal (and not working) : the use of a package to get the min value of the C colum :
############################################################################################
---------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE POC_MIN AS

FUNCTION get_min_POC(p_site    IN NUMBER )
RETURN NUMBER;

END POC_MIN;
/


CREATE OR REPLACE PACKAGE BODY POC_MIN AS

FUNCTION get_min_POC (p_site    IN NUMBER)
 RETURN NUMBER IS
-- PRAGMA AUTONOMOUS_TRANSACTION;  -- useless
o_retour NUMBER :=0 ;
BEGIN

   select min(C)
   into o_retour
   from POC
   where A>100;

    return    o_retour;

    EXCEPTION
    WHEN OTHERS THEN
        IF (sqlcode = -1) THEN
            return -1;--Erreur : Unique constraint violated
        ELSE
            return -2;--Erreur : pble oracle
        END IF;
END get_min_POC;

END POC_MIN;
/
---------------------------------------------------------------------------------------------
to be used with this query (SQL_SELECT_KO3)
SELECT B FROM POC WHERE A>100 and C=POC_MIN.get_min_POC(0) FOR UPDATE



*******/

public class runME {

    static Connection con;
    static ResultSet rs;
    static PreparedStatement ps;
    static Integer record;
    static HashMap hmParam=new HashMap();

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception
    {
        try
        {   // Get param from file
            File configFile = new File(args[0]);
            hmParam = ReadWriteTextFile.fileToHashMap(configFile, ":=");
            //System.out.println(hmParam.entrySet());

            String sIP_DB = hmParam.get("IP_DB").toString();
            int PORT_DB = Integer.parseInt(hmParam.get("PORT_DB").toString());
            String sSID = hmParam.get("SID").toString();
            String sUSR_DB = hmParam.get("USR_DB").toString();
            String sPWD_DB = hmParam.get("PWD_DB").toString();
            String sql_SEL = hmParam.get("SQL_SELECT").toString();

            System.out.println("sql_SEL="+sql_SEL);

            // very small simulation of com.aldata.common.bean.jmsagent.JMSPublisherBean
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            System.out.println("OK");

            System.out.println("jdbc:oracle:thin:@" + sIP_DB + ":" + PORT_DB + ":" + sSID+ "," + sUSR_DB + "," + sPWD_DB);
//          con = DriverManager.getConnection("jdbc:oracle:thin:@demo10g:1521:L10G","HF509","HF509");
            con = DriverManager.getConnection("jdbc:oracle:thin:@" + sIP_DB + ":" + PORT_DB + ":" + sSID, "" + sUSR_DB, "" + sPWD_DB);

            System.out.println("con="+con.toString());

            ps = con.prepareStatement(sql_SEL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            record = 0;
            while(rs.next()) {
                //Some work should happen here.... ;-)
                // while this kind of query may happen, in another oracle session :
                // UPDATE POC set C = MOD(A,100) WHERE  A>100 and C=(select max(C) from POC where A>100;
                System.out.println("Record No"+(record++));
                Thread.sleep(5000);
                rs.deleteRow();
                System.out.println("Done ! It's time to run the UPDATE in another session and commit !");
            }
            System.out.println("Finished !");
        }
        catch (Exception ex) {
                System.out.println("ex="+ex.getMessage());
                ex.printStackTrace();
        }
        finally {
                Thread.sleep(5000);
                rs.close();
                ps.close();
                con.rollback();
        }

    }

}

class ReadWriteTextFile {

  /**
  * Fetch the entire contents of a text file, and return it in a String.
  * This style of implementation does not throw Exceptions to the caller.
  *
  * @param aFile is a file which already exists and can be read.
  */
  static public HashMap fileToHashMap(File aFile,String sep) {
    BufferedReader input = null;
    HashMap<String, String>  hm = new HashMap<String, String>();
    String[] sa;
    try {
      input = new BufferedReader( new FileReader(aFile) );
      String line = null;

      while (( line = input.readLine()) != null){
        sa=line.split(sep);
        hm.put(sa[0],sa[1]);
      }
    }
    catch (FileNotFoundException ex) {
      ex.printStackTrace();
    }
    catch (IOException ex){
      ex.printStackTrace();
    }
    finally {
      try {
        if (input!= null) {
          //flush and close both "input" and its underlying FileReader
          input.close();
        }
      }
      catch (IOException ex) {
        ex.printStackTrace();
      }
    }
    return hm;
  }
}