0

Is there any way to get the Oracle query from PreparedStatement .I know its not possible as such I can't use log4jdbc or p6spy as it is a secured application and using this will create bigger problems..toString won't work as I am using Oracle? I can't change PreparedStatement to Statement either.

Harish
  • 3,343
  • 15
  • 54
  • 75
  • If you cannot change the code, and also not update the configuration to use log4jdbc, how are you going to access the prepared statements? – Thilo Oct 05 '10 at 06:09

3 Answers3

2

If only need for debug time then You can use DebuggableStatement follow this article

pinichi
  • 2,199
  • 15
  • 17
  • yes, but that would require him to change all the places where the statements are prepared. I am assuming that he cannot do that (why not, by the way?). – Thilo Oct 05 '10 at 06:08
  • No I can do that :) Thanks anyway – Harish Oct 05 '10 at 09:26
1

I don't think you should be doing it this way, as there is no officially documented API for this.

If you can mess with the code, why cannot you use log4jdbc ? Oracle JDBC also supports java.util.logging, which you could try to enable.

If you are just interested in the SQL itself, you can turn on session tracing on the Oracle server.

Or maybe you can put your code to where the statement is being prepared (using something like @pinichi is suggesting)?

But just for fun, poking around with the debugger, with my version of Oracle JDBC, I can do

  if (stmt instanceof oracle.jdbc.driver.OraclePreparedStatement) {
        String x = ((oracle.jdbc.driver.OraclePreparedStatement) stmt)
             .getOriginalSql();
        System.out.println(x);
  }
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • This doesn't work at least with Oracle 10g, still prints the ? – Dchucks Mar 22 '12 at 13:47
  • What's wrong with printing the "?" ? That is the query Oracle sees (with maybe ? replaced by :1). What else did you expect? – Thilo Mar 23 '12 at 01:57
  • Ummm like the original question I expect the modified SQL (? replaced with the parameters set) otherwise calling toString() on PreparedStatement would have sufficed. – Dchucks Mar 23 '12 at 06:13
  • It does not say that in the question. Depending on the driver used `toString` can just print `PreparedStatement@0xbdf1234`. And the placeholders do *not* get replaced with the parameters by the driver or the database, they are passed along in a separate data structure (the bind array). Being able to use the same statement repeatedly with many sets of parameters is the whole point of a prepared statement. – Thilo Mar 23 '12 at 07:13
  • Thilo, the original question does indeed ask for the "modified SQL", pl read again. Depends on the driver indeed. Please also see http://stackoverflow.com/questions/3038523/can-i-get-the-full-query-that-a-preparedstatement-is-about-to-execute. – Dchucks Mar 23 '12 at 11:12
1

If you just want to check SQL statement you can also go straight to the database and check v$sql table.

There you can find all sqls and other information about query. More info: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm

Lukasz Stelmach
  • 5,281
  • 4
  • 25
  • 29