1

I'm debugging an issue where I'm running a query against an oracle database and not getting a response. More specifically the query will run forever unless I set a timeout, in which case it will timeout. The query is a prepared statement created by java code using an oracle jdbc driver.

Using wireshark I've tried to capture what is being sent between the database server and the application, and I see a prepared statement similar to this being sent:

SELECT * FROM MY_SCHEMA.MY_TABLE_VIEW
WHERE    DATE_CREATED > :1
AND      DATE_CREATED <= :2
ORDER BY DATE_CREATED

Using wireshark (or any other packet capture tool) is it possible to capture the parameters :1 and :2? Alternately, can I in any other way verify that they reach the database server?

Øyvind Strømmen
  • 31
  • 6
  • 2
    You currently have the query string that was **prepared**. The data is sent **separately** on **execute** (probably as a binary encoding of the date information). Wireshark probably cannot decode that for you and combine it with the query string of the prepared statement. You may want to look at JDBC proxy solutions like p6spy. – Mark Rotteveel Jun 17 '19 at 11:54
  • 3
    If you have access to the database you can check the active queries in the database as shown [here](https://dba.stackexchange.com/questions/8828/how-do-you-show-sql-executing-on-an-oracle-database). – Rashin Jun 17 '19 at 12:08
  • @MarkRotteveel Will p6spy help me determine if the data is actually sent over the wire? At this stage, what the data is, isn't that important. I'm more interested in seeing whether it reaches the server. – Øyvind Strømmen Jun 17 '19 at 12:40
  • @Rashin Unfortunately I don't have access. – Øyvind Strømmen Jun 17 '19 at 12:45
  • 1
    @Mark raises a good point about the date information possibly being sent as binary, which would make it harder to see in the packet trace. Perhaps try a little test query like `SELECT ? AS mylastname, ? AS mydatevalue FROM DUAL`, pass your last name as the first parameter and some date value as the second, and then see if you can find your last name in the packet trace. That might tell you if the JDBC driver is sending the query string and the parameter values at the same time (as mssql-jdbc often does by calling `sp_prepexec`) or if they get sent separately. – Gord Thompson Jun 17 '19 at 13:08
  • 1
    @GordThompson Good idea. I tried the query, and apparently the driver sends the parameters at the same time, cause I'm seeing my last name at the end of the same packet as the query. Thanks a lot, that tells me what I wanted to know. – Øyvind Strømmen Jun 17 '19 at 13:41
  • @ØyvindStrømmen , to add about wireshark . i tried with wireshark . i am able to get both query and parameter ( at the end of packet). whats the problem is , PreparedStatement executed for first time doesn't have any issues but from second time onwards , its only sending parameters not the whole query. want to understand the reason for it. – anavaras lamurep Aug 07 '19 at 09:34

0 Answers0