Re: Trying to"decode" an Oracle data packet

On Fri, 27 Feb 2004 10:51:57 +1100, "Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote:

>I'm getting incorrect results from a custom-built software package which
>uses Oracle as the database. So before I call the techos who charge more
>than a wounded bull, I'm endeavouring to find the problem.
>
>I'm using a packet sniffer which is picking up data packets b/w the Oracle
>server and my workstation. Here's the SQL which is being sent to Oracle:
>
>SELECT C.FACTOR
> FROM COST_PERD A, COST_REF B, COST_REF_CZ C
> WHERE A.RCIDX = :1 AND A.FROMD <= :2
> AND A.TOD >= :3 AND A.CPIDX = B.CPIDX
> AND B.MAN_CZIDX = :4 AND B.CRIDX = C.CRIDX
> AND C.DEST_CZIDX = :5 ;
>
>You will notice that ":2" & ":3" are variables used by the program (along
>with the other ones), and are send in another data packet to the Oracle
>server seperately. BTW, this problem, which I am about to explain, happens
>with ALL date-criteria in ALL SQLs.
>
>Here is the decoded data packet:
>
>Packet data:
>0000: 00 08 C7 DB A1 9F 00 0D 61 04 DC EC 08 00 45 00 ........a.....E.
>0010: 01 59 A1 84 40 00 80 06 D4 71 C0 A8 01 55 C0 A8 .Y..@....q...U..
>0020: 01 03 0D A4 04 CD 4F A6 C6 92 21 5D DA 03 50 18 ......O...!]..P.
>0030: F5 68 8C 97 00 00 01 31 00 00 06 00 00 00 00 00 .h.....1........
>0040: 03 47 DB 38 80 00 00 01 00 00 00 00 00 00 00 00 .G.8............
>0050: 00 00 00 00 00 00 00 00 00 00 00 E8 CB 36 00 07 .............6..
>0060: 00 00 00 24 AD 36 00 02 00 00 00 00 00 00 00 48 ...$.6.........H
>0070: 59 F2 02 01 00 00 00 4C 4E F2 02 05 00 00 00 00 Y......LN.......
>0080: 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 00 ................
>0090: 00 00 00 00 00 00 00 00 00 00 00 01 01 07 00 00 ................
>00A0: 15 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
>00B0: 00 00 00 00 1F 00 00 00 01 01 21 00 00 04 00 00 ..........!.....
>00C0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
>00D0: 00 1F 00 00 00 01 0C 01 00 00 01 00 00 00 00 00 ................
>00E0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
>00F0: 00 00 01 0C 01 00 00 01 00 00 00 00 00 00 00 00 ................
>0100: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 ................
>0110: 01 21 00 00 03 00 00 00 00 00 00 00 00 00 00 00 .!..............
>0120: 00 00 00 00 00 00 00 00 1F 00 00 00 01 01 21 00 ..............!.
>0130: 00 03 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
>0140: 00 00 00 00 00 1F 00 00 00 07 04 34 33 39 37 07 ...........4397.
>0150: 77 C7 0C 17 01 01 01 07 77 C7 0C 17 01 01 01 03 w.......w.......
>0160: 31 35 38 03 31 35 38
>158.158
>
>From the above dump, the variables assume the following values:
>
>:1 = 4397
>:2 = w......
>:3 = w......
>:4 = 158
>:5 = 158
>
>How do I decode the date values for ":2" & ":3"? I know they are dates, but
>can't figger out what the dates are.

Actually you don't need packet sniffers at all. sqlnet can trace and Oracle can trace.
In this specific case, setting event 10046 for the process at hand is sufficient.
Find the process id of the affected server process (or the sid and the serial)
then issue (connected as sysdba)
oradebug setospid <your processid>
oradebug unlimit
oradebug event 10046 trace name context forever, level 12

and there you go.
If you have the sid and the serial, dbms_system.set_ev does exactly the same.

--
Sybrand Bakker, Senior Oracle DBA