Home » SQL & PL/SQL » SQL & PL/SQL » SQL query with the parameters substituted
SQL query with the parameters substituted [message #263173] Wed, 29 August 2007 04:52 Go to next message
gurumurthy4
Messages: 17
Registered: August 2007
Junior Member
I want to get the query with the parameters substituted to be get from the java application running....
eg:
PreparedStatement ps2=con.prepareStatement("select sql_fulltext from v$sql where sql_id=?");
ps2.setString(1,sql_id);
ResultSet rs2=ps2.executeQuery();

I can get the sql_id using sys)context()
But still its showing with ? in it..
Is there any way to get the complete query from the driver itself since the substitution is done somewhere in the application itself.. I need that information in detail too... Where or How the parameters are substituted?
Re: SQL query with the parameters substituted [message #263181 is a reply to message #263173] Wed, 29 August 2007 05:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
PreparedStatement ps2=con.prepareStatement("select sql_fulltext from v$sql where sql_id=?");


Instead of ? you have to use query

reparedStatement ps2=con.prepareStatement("select sql_fulltext from v$sql where sql_id=.......
Re: SQL query with the parameters substituted [message #263183 is a reply to message #263173] Wed, 29 August 2007 05:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
Where or How the parameters are substituted?

By the way preparestatement not necessary, you can use,
static Statement stmt;
String test="select * from test1 where
id = (select id from table where code = '" + Constants.CODE+ "')"

stmt = connection.createStatement();
ResultSet rsSeq = stmt.executeQuery(String);



See my example . I have showed two ways to pass parameters in it.in the constants class I have hard coded it. However you can get by query "select id from table " as shown above.
Re: SQL query with the parameters substituted [message #263199 is a reply to message #263173] Wed, 29 August 2007 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can query V$SQL_BIND_CAPTURE view (with addition information in V$SQL_BIND_DATA and V$SQL_BIND_METADATA).

Regards
Michel

Re: SQL query with the parameters substituted [message #263201 is a reply to message #263183] Wed, 29 August 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju wrote on Wed, 29 August 2007 12:26
Quote:
Where or How the parameters are substituted?

By the way preparestatement not necessary, you can use,
static Statement stmt;
String test="select * from test1 where
id = (select id from table where code = '" + Constants.CODE+ "')"

stmt = connection.createStatement();
ResultSet rsSeq = stmt.executeQuery(String);


NEVER ever do that (but for very specific cases). You will fill your SGA with duplicate queries and dramatically slow down your server. Always use a PrepareStatement.

Regards
Michel

Re: SQL query with the parameters substituted [message #263229 is a reply to message #263201] Wed, 29 August 2007 07:56 Go to previous messageGo to next message
gurumurthy4
Messages: 17
Registered: August 2007
Junior Member
Is there no other way to get the substituted query? I needed for the query having a preparedstatement and parameters in it..
Getting from the tables are fine.... But from the application perspective, it is time expensive... Are there any other way from which we can get by using the JDBC driver classes itself?
Re: SQL query with the parameters substituted [message #263236 is a reply to message #263229] Wed, 29 August 2007 08:26 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Er! Put it in a global variable?

Regards
Michel
Previous Topic: Not getting the result
Next Topic: can we write a "SWITCH" statement in PL/SQL block?
Goto Forum:
  


Current Time: Thu Dec 08 16:33:27 CST 2016

Total time taken to generate the page: 0.12344 seconds