Don't do DDL with PreparedStatement with Oracle!

Hi all.

I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
MY_TABLE",
so they did:

ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");


This statement *worked fine* the first time they executed it, but
fascinatingly,
every subsequent execution of that statement *silently failed, doing
*nothing*!
The driver got the expected response packet from the DBMS, but Oracle
did
nothing. The customer could even continue to execute this statement,
seemingly
successfully, after they completely dropped the table!
The case was complicated because their code actually created,
executed,
and closed the statement each time, but because the customer was using
a
connection pooling system (like BEA's or Oracle's own JDBC driver),
that will
transparently cache and re-use PreparedStatements (a big performance
feature),
the application would get the same statement under the covers, and
start
getting the odd NO-OP behavior.
Oracle's driver and our pooling are both unable to do anything about
this,
when the SQL is created at runtime by the customer, and this DBMS bug
would force the non-caching of statements for anyone sending DDL, like
that. To reiterate, this is an Oracle DBMS bug. It can be reproduced
with
a simple JDBC client or an OCI+C program, but not SQL-PLUS because
SQL-PLUS never prepares statements....

FYI,
Joe Weinstein at BEA Systems

(I don't know why the original was so badly formatted)
Hi all.

I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
MY_TABLE", so they did:

ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");

This statement *worked fine* the first time they executed it, but
fascinatingly, every subsequent execution of that statement
*silently failed, doing *nothing*! The driver got the expected
response packet from the DBMS, but Oracle did nothing. The
customer could even continue to execute this statement, seemingly
successfully, after they completely dropped the table!
The case was complicated because their code actually created,
executed, and closed the statement each time, but because the
customer was using a connection pooling system (like BEA's or
Oracle's own JDBC driver), that will transparently cache and
re-use PreparedStatements (a big performance feature), the
application would get the same statement under the covers, and
start getting the odd NO-OP behavior.
Oracle's driver and our pooling are both unable to do anything
about this, when the SQL is created at runtime by the customer,
and this DBMS bug would force the non-caching of statements for
anyone sending DDL, like that. To reiterate, this is an Oracle
DBMS bug. It can be reproduced with a simple JDBC client or an
OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
statements....

FYI,
Joe Weinstein at BEA Systems

On Feb 13, 3:43*pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
wrote:
> (I don't know why the original was so badly formatted)
> Hi all.
>
> I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> MY_TABLE", so they did:
>
> ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> This statement *worked fine* the first time they executed it, but
> fascinatingly, every subsequent execution of that statement
> *silently failed, doing *nothing*! The driver got the expected
> response packet from the DBMS, but Oracle did nothing. The
> customer could even continue to execute this statement, seemingly
> successfully, after they completely dropped the table!
> * *The case was complicated because their code actually created,
> executed, and closed the statement each time, but because the
> customer was using a connection pooling system (like BEA's or
> Oracle's own JDBC driver), that will transparently cache and
> re-use PreparedStatements (a big performance feature), the
> application would get the same statement under the covers, and
> start getting the odd NO-OP behavior.
> * Oracle's driver and our pooling are both unable to do anything
> about this, when the SQL is created at runtime by the customer,
> and this DBMS bug would force the non-caching of statements for
> anyone sending DDL, like that. To reiterate, this is an Oracle
> DBMS bug. It can be reproduced with a simple JDBC client or an
> OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> statements....
>
> FYI,
> Joe Weinstein at BEA Systems

It is documented that DDLs should be re-prepared however, I found out
that the OCI driver manages to avoid re-parsing of DDL, throug its
statement cache. The Oracle JDBC team will look into doing the same.

Kuassi
Oracle JDBC product managemenent
http://db360.blogspot.com

On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
<kuassi.men...@gmail.com> wrote:
> On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
> wrote:
>
>
>
> > (I don't know why the original was so badly formatted)
> > Hi all.
>
> > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > MY_TABLE", so they did:
>
> > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > This statement *worked fine* the first time they executed it, but
> > fascinatingly, every subsequent execution of that statement
> > *silently failed, doing *nothing*! The driver got the expected
> > response packet from the DBMS, but Oracle did nothing. The
> > customer could even continue to execute this statement, seemingly
> > successfully, after they completely dropped the table!
> > The case was complicated because their code actually created,
> > executed, and closed the statement each time, but because the
> > customer was using a connection pooling system (like BEA's or
> > Oracle's own JDBC driver), that will transparently cache and
> > re-use PreparedStatements (a big performance feature), the
> > application would get the same statement under the covers, and
> > start getting the odd NO-OP behavior.
> > Oracle's driver and our pooling are both unable to do anything
> > about this, when the SQL is created at runtime by the customer,
> > and this DBMS bug would force the non-caching of statements for
> > anyone sending DDL, like that. To reiterate, this is an Oracle
> > DBMS bug. It can be reproduced with a simple JDBC client or an
> > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > statements....
>
> > FYI,
> > Joe Weinstein at BEA Systems
>
> It is documented that DDLs should be re-prepared however, I found out
> that the OCI driver manages to avoid re-parsing of DDL, throug its
> statement cache. The Oracle JDBC team will look into doing the same.
>
> Kuassi
> Oracle JDBC product managemenenthttp://db360.blogspot.com

Hi Kuassi! Thanks. I don't understand though... If I am correct,
the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
the DBMS will do nothing. Any client-side caching would simply
aggravate the problem, making a user's re-prepare get the same
now-unfunctional statement from the cache.
Joe

On Feb 14, 9:27*am, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
wrote:
> On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
>
>
>
>
>
> <kuassi.men...@gmail.com> wrote:
> > On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
> > wrote:
>
> > > (I don't know why the original was so badly formatted)
> > > Hi all.
>
> > > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > > MY_TABLE", so they did:
>
> > > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > > This statement *worked fine* the first time they executed it, but
> > > fascinatingly, every subsequent execution of that statement
> > > *silently failed, doing *nothing*! The driver got the expected
> > > response packet from the DBMS, but Oracle did nothing. The
> > > customer could even continue to execute this statement, seemingly
> > > successfully, after they completely dropped the table!
> > > * *The case was complicated because their code actually created,
> > > executed, and closed the statement each time, but because the
> > > customer was using a connection pooling system (like BEA's or
> > > Oracle's own JDBC driver), that will transparently cache and
> > > re-use PreparedStatements (a big performance feature), the
> > > application would get the same statement under the covers, and
> > > start getting the odd NO-OP behavior.
> > > * Oracle's driver and our pooling are both unable to do anything
> > > about this, when the SQL is created at runtime by the customer,
> > > and this DBMS bug would force the non-caching of statements for
> > > anyone sending DDL, like that. To reiterate, this is an Oracle
> > > DBMS bug. It can be reproduced with a simple JDBC client or an
> > > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > > statements....
>
> > > FYI,
> > > Joe Weinstein at BEA Systems
>
> > It is documented that DDLs should be re-prepared however, I found out
> > that the OCI driver manages to avoid re-parsing of DDL, throug its
> > statement cache. The Oracle JDBC team will look into doing the same.
>
> > Kuassi
> > Oracle JDBC product managemenenthttp://db360.blogspot.com
>
> Hi Kuassi! Thanks. I don't understand though... If I am correct,
> the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
> the DBMS will do nothing. Any client-side caching would simply
> aggravate the problem, making a user's re-prepare get the same
> now-unfunctional statement from the cache.
> Joe- Hide quoted text -
>
> - Show quoted text -

Mea culpa; in fact OCI mark the DDL for re-parsing, which avoids the
no-op on subsequent execution.

Kuassi

On Feb 14, 11:39 am, "kuassi.men...@gmail.com"
<kuassi.men...@gmail.com> wrote:
> On Feb 14, 9:27 am, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
> wrote:
>
>
>
> > On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
>
> > <kuassi.men...@gmail.com> wrote:
> > > On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
> > > wrote:
>
> > > > (I don't know why the original was so badly formatted)
> > > > Hi all.
>
> > > > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > > > MY_TABLE", so they did:
>
> > > > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > > > This statement *worked fine* the first time they executed it, but
> > > > fascinatingly, every subsequent execution of that statement
> > > > *silently failed, doing *nothing*! The driver got the expected
> > > > response packet from the DBMS, but Oracle did nothing. The
> > > > customer could even continue to execute this statement, seemingly
> > > > successfully, after they completely dropped the table!
> > > > The case was complicated because their code actually created,
> > > > executed, and closed the statement each time, but because the
> > > > customer was using a connection pooling system (like BEA's or
> > > > Oracle's own JDBC driver), that will transparently cache and
> > > > re-use PreparedStatements (a big performance feature), the
> > > > application would get the same statement under the covers, and
> > > > start getting the odd NO-OP behavior.
> > > > Oracle's driver and our pooling are both unable to do anything
> > > > about this, when the SQL is created at runtime by the customer,
> > > > and this DBMS bug would force the non-caching of statements for
> > > > anyone sending DDL, like that. To reiterate, this is an Oracle
> > > > DBMS bug. It can be reproduced with a simple JDBC client or an
> > > > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > > > statements....
>
> > > > FYI,
> > > > Joe Weinstein at BEA Systems
>
> > > It is documented that DDLs should be re-prepared however, I found out
> > > that the OCI driver manages to avoid re-parsing of DDL, throug its
> > > statement cache. The Oracle JDBC team will look into doing the same.
>
> > > Kuassi
> > > Oracle JDBC product managemenenthttp://db360.blogspot.com
>
> > Hi Kuassi! Thanks. I don't understand though... If I am correct,
> > the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
> > the DBMS will do nothing. Any client-side caching would simply
> > aggravate the problem, making a user's re-prepare get the same
> > now-unfunctional statement from the cache.
> > Joe- Hide quoted text -
>
> > - Show quoted text -
>
> Mea culpa; in fact OCI mark the DDL for re-parsing, which avoids the
> no-op on subsequent execution.
>
> Kuassi

Interesting... So how does OCI *know* it's DDL? Or is OCI marking
*every* statement for re-parse? Wouldn't that be a big performance
regression, asking the DBMS to re-parse everything, even the DML
statements that don't need it?