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

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Fri, 8 Feb 2008 10:55:32 -0800 (PST)
Message-ID: <c5cd3d93-a5ac-497a-a90d-ffe430c86e25@m34g2000hsb.googlegroups.com>


On Feb 8, 1:47 pm, "joeNOS..._at_BEA.com" <joe.weinst..._at_gmail.com> wrote:
> 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 : " +
> prep_trunc.executeUpdate());
>
> 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 : " +
> prep_trunc.executeUpdate());
>
> rs = prep_sel.executeQuery();
> rs.next();
> System.out.println("count(*) returns " + rs.getString(1) );

Yep, you are correct.

BTW, here is the thread I was referencing...

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/27039391443554b2/2e395c8864a354ec Received on Fri Feb 08 2008 - 12:55:32 CST

Original text of this message