Hello! This question is about using Wireshark to read the contents of TNS packets. I have no problem tracing an Oracle session, decoding the TNS, and finding the query text (select from some table). What puzzles me is why I see "ORA-01403: no data found" in the response from the database server even when the query has a result set. Have I missed a set-up step? I am evaluating the use of SQLNET encryption, but first I want to understand the problem. Thank you for your help. asked 23 Jan '13, 05:10  thomaswireshark |
2 Answers:
I have not looked at TNS packets much, but could it be that SQLNET is iterating through the rows of the query result and gives this message after the last row? Anyways, this seems a little off topic to me as it is more of a SQLNET question than a Wireshark Question... answered 23 Jan '13, 12:51  SYN-bit ♦♦ |
When doing a selece query, ORA no data found message indicate that you're at the end of your query (last row of your query). So it's not an error or anything. Are you troubleshooting an issue or just curious about the no data found message? answered 24 Jan '13, 16:13  hansangb |
Thank you very much for your reply and idea, a good one. I can be more specific with my question. Wireshark dissects the column headers (labels, names), but gives "data not found" instead of data. I wonder if the dissector source might have some clues to what Wireshark displays? Perhaps I could also put this post more on topic by rephrasing the question to ask how I can use Wireshark to view full TNS payloads. Here is what I have for the Wireshark TNS dissector code:
http://anonsvn.wireshark.org/wireshark/trunk/epan/dissectors/packet-tns.c
As the string "Data not found" is not present in the dissector, might it be a literally string in the SQLNET output?
Are you able to share the tracefile on www.cloudshark.org or does it contain sensitive data?
I have pretty much figured this out. My test was on a single-row table with a single numeric column. It appears that Wireshark balks at numbers in the SQLNET payload. I base this statement on the fact that the number appeared in the packet payload after I converted it to a character in the SQL. In my first test, it only appeared that I was not getting any data; in fact, the "ORA-01403: no data found" only means "end of data" basically - as you astutely suggested. So certainly it is true that without SQLNET encryption all that sensitive Oracle HR data travels in the clear. Thanks for your help!
Interesting ... It's not Wireshark, or, for that matter, Netmon. Direct SQLNET client tracing in Oracle produces the same results: numbers and dates do not show in the trace results unless you first convert them to character format (TO_CHAR) in the SQL.