Re: A big favor to ask of any C+OCI programmer to show a DBMS bug...

On Feb 8, 10:26 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Feb 8, 12:51 pm, "joeNOS..._at_BEA.com" <joe.weinst..._at_gmail.com>
> wrote:
>
> Hi Joe,
>
> Oracle Server 10.2.0.3, with *11.1.0.6* drivers (I didn't have the
> older ones handy)
>
> Unless I am misunderstanding, I couldn't even duplicate your test case
> using JDBC drivers. I didn't use terminal input with waits, I just
> embedded a sleep call that gave me time (two minutes) to run the
> second insert in SQL*PLUS...
>
> import java.sql.*;
>
> public class testForJoe {
> public static void main(String args[]) {
> try {
> Class.forName("oracle.jdbc.driver.OracleDriver");
> Connection conn =
> DriverManager.getConnection("jdbc:oracle:thin:@xxx",
> "xxx",
> "xxx");
>
> System.out.println(conn.getMetaData().getDriverVersion());
> Statement stm = conn.createStatement();
> stm.execute("truncate table test");
> System.out.println("Executed first truncate...");
> Thread.sleep(120000);
> stm.execute("truncate table test");
> System.out.println("Executed second truncate...");
> }
> catch(Exception e) {
> e.printStackTrace();
> }
> }
>
> }
>
> ...with the output below...
>
> C:\java>java testForJoe
> 11.1.0.6.0-Production
> Executed first truncate...
> Executed second truncate...
>
> C:\java>
>
> ...and the SQL*PLUS session, with steps taken in the order you
> requested...
>
> SQL> create table test(c number);
>
> Table created.
>
> SQL> insert into test values(1);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from test;
>
> C
> ----------
> 1
>
> SQL> select * from test;
>
> no rows selected
>
> SQL> insert into test values(2);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from test;
>
> C
> ----------
> 2
>
> SQL> r
> 1* select * from test
>
> no rows selected
>
> SQL>
>
> I do remember a whole thread going through CDOS about 18 months or so
> ago (maybe 12, I don't know) that had to do with TRUNCATE behavior
> when the isolation level was serializable.
>
> HTH,
>
> Steve

Hi Steve! The key is to use a *PreparedStatement* for the truncate call,
and re-execute it.

     Statement s = c.createStatement();
     try { s.executeUpdate("drop table truncate_test_tb"); } catch
(Exception ignore){}
     s.executeUpdate("create table truncate_test_tb (foo DATE)");

     PreparedStatement prep_ins =
        c.prepareStatement("insert into truncate_test_tb
values(SYSDATE)");
     System.out.println("PREP insert : " + prep_ins.executeUpdate());

     PreparedStatement prep_trunc = c.prepareStatement("truncate table
truncate_test_tb");
     System.out.println("PreparedStatement truncate : " +

     PreparedStatement prep_sel =
       c.prepareStatement("select count(*) from truncate_test_tb");

     ResultSet rs = prep_sel.executeQuery();
     rs.next();
     System.out.println("count(*) returns " + rs.getString(1) );

     System.out.println("PREP insert : " + prep_ins.executeUpdate());

     System.out.println("PreparedStatement truncate : " +

     rs = prep_sel.executeQuery();
     rs.next();
     System.out.println("count(*) returns " + rs.getString(1) );