Re: Trying to"decode" an Oracle data packet

Thanks Sybrand,

But i'm not an Oracle DBA. I don't understand what you have said (not to say that it's most probably a handy tool when in the hands of someone who know what to do with it).

My knowledge is limited with Oracle, but I know my WinNT servers pretty good, being an all-round network admin person. I have done a few things with Oracle b4, but always under instruction!

-- 

Prince Of Thieves



"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:1m1t305ucoiefd5s1maacs0f261oc7ji1g_at_4ax.com...


> 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