## Question and Answer

## You Asked

Tom,

I'm trying to find a bind variable value in a trace file. Here is a snapshot:

PARSING IN CURSOR #40 len=708 dep=0 uid=260 oct=3 lid=260 tim=9922116459716 hv=256108709 ad='8fbbcdf0'

select leaves0_.TRIPNUM as TRIPNUM1_, leaves0_.LVDATE as LVDATE1_, leaves0_.U##VCHNUM as U1_1_, leaves0_.U##SSN as U2_1_,

leaves0_.ADJ_LEVEL as ADJ4_1_, leaves0_.U##DOCTYPE as U3_1_, leaves0_.U##VCHNUM as U1_56_0_, leaves0_.U##SSN as U2_56_0_,

leaves0_.U##DOCTYPE as U3_56_0_, leaves0_.ADJ_LEVEL as ADJ4_56_0_, leaves0_.TRIPNUM as TRIPNUM56_0_, leaves0_.LVDATE as LVDATE56_0_,

leaves0_.VCHNUM as VCHNUM56_0_, leaves0_.SSN as SSN56_0_, leaves0_.DOCTYPE as DOCTYPE56_0_, leaves0_.LVTYPE as LVTYPE56_0_,

leaves0_.LVHOUR as LVHOUR56_0_ from FRED.TLEAVE leaves0_

where leaves0_.TRIPNUM=:1 and leaves0_.LVDATE=:2 and leaves0_.U##VCHNUM=:3

and leaves0_.U##SSN=:4 and leaves0_.ADJ_LEVEL=:5 and leaves0_.U##DOCTYPE=:6

END OF STMT

EXEC #40:c=0,e=1039,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9922116459708

FETCH #40:c=0,e=19948,p=2,cr=2,cu=0,mis=0,r=0,dep=0,og=1,tim=9922116479812

BINDS #40:

kkscoacd

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=160 off=0

kxsbbbfp=ffffffff7ad37f40 bln=22 avl=02 flg=05

value=1

Bind#1

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=24

kxsbbbfp=ffffffff7ad37f58 bln=11 avl=07 flg=01

value=

Dump of memory from 0xFFFFFFFF7AD37F58 to 0xFFFFFFFF7AD37F5F

FFFFFFFF7AD37F50 786B0C1A 010101E0 [xk......]

Bind#2

oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=40

kxsbbbfp=ffffffff7ad37f68 bln=32 avl=21 flg=01

value="GODETROITMI122307_A01"

Bind#3

oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=72

kxsbbbfp=ffffffff7ad37f88 bln=32 avl=09 flg=01

value="XXXXX0007"

Bind#4

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=104

kxsbbbfp=ffffffff7ad37fa8 bln=22 avl=01 flg=01

value=0

Bind#5

oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=128

kxsbbbfp=ffffffff7ad37fc0 bln=32 avl=04 flg=01

value="AUTH"

I'm interested in a value of Bind#1 variable (leaves0_.LVDATE=:2). This column is defined as a date in tleave table. According to v$sql_bind_capture, this bind variable has datatype_string='TIMESTAMP'.

However this is the only variable that seems not to have a value. In one of your postings on this site you showed an example when value was null. However in this case, it also shows a dump:

Dump of memory from 0xFFFFFFFF7AD37F58 to 0xFFFFFFFF7AD37F5F

FFFFFFFF7AD37F50 786B0C1A 010101E0 [xk......]

I'm wondering if real value of a bind variable is hidden somewhere in this dump, and if there is a way to extract it.

I'm trying to find a bind variable value in a trace file. Here is a snapshot:

PARSING IN CURSOR #40 len=708 dep=0 uid=260 oct=3 lid=260 tim=9922116459716 hv=256108709 ad='8fbbcdf0'

select leaves0_.TRIPNUM as TRIPNUM1_, leaves0_.LVDATE as LVDATE1_, leaves0_.U##VCHNUM as U1_1_, leaves0_.U##SSN as U2_1_,

leaves0_.ADJ_LEVEL as ADJ4_1_, leaves0_.U##DOCTYPE as U3_1_, leaves0_.U##VCHNUM as U1_56_0_, leaves0_.U##SSN as U2_56_0_,

leaves0_.U##DOCTYPE as U3_56_0_, leaves0_.ADJ_LEVEL as ADJ4_56_0_, leaves0_.TRIPNUM as TRIPNUM56_0_, leaves0_.LVDATE as LVDATE56_0_,

leaves0_.VCHNUM as VCHNUM56_0_, leaves0_.SSN as SSN56_0_, leaves0_.DOCTYPE as DOCTYPE56_0_, leaves0_.LVTYPE as LVTYPE56_0_,

leaves0_.LVHOUR as LVHOUR56_0_ from FRED.TLEAVE leaves0_

where leaves0_.TRIPNUM=:1 and leaves0_.LVDATE=:2 and leaves0_.U##VCHNUM=:3

and leaves0_.U##SSN=:4 and leaves0_.ADJ_LEVEL=:5 and leaves0_.U##DOCTYPE=:6

END OF STMT

EXEC #40:c=0,e=1039,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9922116459708

FETCH #40:c=0,e=19948,p=2,cr=2,cu=0,mis=0,r=0,dep=0,og=1,tim=9922116479812

BINDS #40:

kkscoacd

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=160 off=0

kxsbbbfp=ffffffff7ad37f40 bln=22 avl=02 flg=05

value=1

Bind#1

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=24

kxsbbbfp=ffffffff7ad37f58 bln=11 avl=07 flg=01

value=

Dump of memory from 0xFFFFFFFF7AD37F58 to 0xFFFFFFFF7AD37F5F

FFFFFFFF7AD37F50 786B0C1A 010101E0 [xk......]

Bind#2

oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=40

kxsbbbfp=ffffffff7ad37f68 bln=32 avl=21 flg=01

value="GODETROITMI122307_A01"

Bind#3

oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=72

kxsbbbfp=ffffffff7ad37f88 bln=32 avl=09 flg=01

value="XXXXX0007"

Bind#4

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=104

kxsbbbfp=ffffffff7ad37fa8 bln=22 avl=01 flg=01

value=0

Bind#5

oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=128

kxsbbbfp=ffffffff7ad37fc0 bln=32 avl=04 flg=01

value="AUTH"

I'm interested in a value of Bind#1 variable (leaves0_.LVDATE=:2). This column is defined as a date in tleave table. According to v$sql_bind_capture, this bind variable has datatype_string='TIMESTAMP'.

However this is the only variable that seems not to have a value. In one of your postings on this site you showed an example when value was null. However in this case, it also shows a dump:

Dump of memory from 0xFFFFFFFF7AD37F58 to 0xFFFFFFFF7AD37F5F

FFFFFFFF7AD37F50 786B0C1A 010101E0 [xk......]

I'm wondering if real value of a bind variable is hidden somewhere in this dump, and if there is a way to extract it.

## and Tom said...

say you have:

In my trace file, I'll find:

so, you see that bit:

so that 4 bytes is reversed ordered (ON MY SYSTEM - you might have other byte ordering) and is the first 4 bytes of the DUMP you see in the comment.

then the word after that is the next 4 bytes in reverse.

then on the next line, we have the next three bytes (0, 75, 88)

so, it is there, but totally encoded in hex - I'm afraid you would either

a) have to decode this yourself

b) file an enhancement request to decode this in the trace file.

Now, you have:

that is only 7 bytes (so it is a timestamp with zero fractional digits)

Ok, for the timestamp date bit, lets see if we can decode this. It looks like you are on a system that has the reverse byte order from me (because yours starts with 78b6 - that is the century and year...

Now, the century and year are in excess 100 notation - we subtract 100 - and if the number is zero - we are BC, else we are AD. So, the year component of that is apparently

2007 = (120-100) || (107-100)

Next we have

those are the month and day fields - they are stored "naturally" - so that was month=12, day=26

So, so far we has 26-dec-2007

The next three are the hour, minute and second - and they are stored in excess-1 notation - we subtract 1 - you had:

that would be midnight (hour = 1-1, minute = 1-1, second = 1-1 )

so, your timestamp is simply 26-dec-2007 of course!!!

You can use something like this:

if you have fractional seconds, you just take the next 4 bytes and convert them to decimal - that is the fractional portion if you need it.

create table t ( x timestamp ); insert into t values ( systimestamp ); exec dbms_stats.gather_table_stats( user, 'T' ); declare l_data varchar2(255); l_bind timestamp; c sys_refcursor; begin select dump(x,16), x into l_data, l_bind from t; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; open c for 'select * from t /* ' || l_data || ' */ where x = :x' using l_bind; fetch c into l_data; close c; execute immediate 'alter session set events ''10046 trace name context off'''; end; /

In my trace file, I'll find:

PARSING IN CURSOR #8 len=80 dep=1 uid=233 oct=3 lid=233 tim=1170010001703316 hv=853505792 ad='3b697634' select * from t /* Typ=180 Len=11: 78,6b,c,13,e,33,2a,20,0,75,88 */ where x = :x END OF STMT PARSE #8:c=1000,e=740,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1170010001703305 BINDS #8: kkscoacd Bind#0 oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=06 pre=00 oacflg=01 fl2=1206001 frm=00 csi=06 siz=12 off=0 kxsbbbfp=b7218be8 bln=11 avl=11 flg=05 value= Dump of memory from 0xB7218BE8 to 0xB7218BF3 B7218BE0 130C6B78 202A330E [xk...3* ] B7218BF0 00887500 [.u..] EXEC #8:c=1000,e=1347,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1170010001704835

so, you see that bit:

B7218BE0 130C6B78 202A330E [xk...3* ] ^^^^^^^^

so that 4 bytes is reversed ordered (ON MY SYSTEM - you might have other byte ordering) and is the first 4 bytes of the DUMP you see in the comment.

then the word after that is the next 4 bytes in reverse.

then on the next line, we have the next three bytes (0, 75, 88)

so, it is there, but totally encoded in hex - I'm afraid you would either

a) have to decode this yourself

b) file an enhancement request to decode this in the trace file.

Now, you have:

Bind#1 oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=24 kxsbbbfp=ffffffff7ad37f58 bln=11 avl=07 flg=01 value= Dump of memory from 0xFFFFFFFF7AD37F58 to 0xFFFFFFFF7AD37F5F FFFFFFFF7AD37F50 786B0C1A 010101E0 [xk......]

that is only 7 bytes (so it is a timestamp with zero fractional digits)

Ok, for the timestamp date bit, lets see if we can decode this. It looks like you are on a system that has the reverse byte order from me (because yours starts with 78b6 - that is the century and year...

ops$tkyte%ORA10GR2> select to_number( '78', 'xx' ), to_number('6b', 'xx' ) from dual; TO_NUMBER('78','XX') TO_NUMBER('6B','XX') -------------------- -------------------- 120 107

Now, the century and year are in excess 100 notation - we subtract 100 - and if the number is zero - we are BC, else we are AD. So, the year component of that is apparently

2007 = (120-100) || (107-100)

Next we have

ops$tkyte%ORA10GR2> select to_number( '0c', 'xx' ), to_number('1a', 'xx' ) from dual; TO_NUMBER('0C','XX') TO_NUMBER('1A','XX') -------------------- -------------------- 12 26

those are the month and day fields - they are stored "naturally" - so that was month=12, day=26

So, so far we has 26-dec-2007

The next three are the hour, minute and second - and they are stored in excess-1 notation - we subtract 1 - you had:

010101

that would be midnight (hour = 1-1, minute = 1-1, second = 1-1 )

so, your timestamp is simply 26-dec-2007 of course!!!

You can use something like this:

ops$tkyte%ORA10GR2> create or replace function hex_to_date( p_str in varchar2 ) return timestamp 2 as 3 begin 4 return to_timestamp( 5 to_char( to_number( substr( p_str, 1, 2 ), 'xx' ) - 100, 'fm00' ) || 6 to_char( to_number( substr( p_str, 3, 2 ), 'xx' ) - 100, 'fm00' ) || 7 to_char( to_number( substr( p_str, 5, 2 ), 'xx' ), 'fm00' ) || 8 to_char( to_number( substr( p_str, 7, 2 ), 'xx' ), 'fm00' ) || 9 to_char( to_number( substr( p_str,10, 2 ), 'xx' )-1, 'fm00' ) || 10 to_char( to_number( substr( p_str,12, 2 ), 'xx' )-1, 'fm00' ) || 11 to_char( to_number( substr( p_str,14, 2 ), 'xx' )-1, 'fm00' ), 'yyyymmddhh24miss' ); 12 end; 13 / Function created. ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> select hex_to_date( '786B0C1A 010101E0' ) from dual; HEX_TO_DATE('786B0C1A010101E0') --------------------------------------------------------------------------- 26-DEC-07 12.00.00.000000000 AM

if you have fractional seconds, you just take the next 4 bytes and convert them to decimal - that is the fractional portion if you need it.

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

Great answer! Thanks a lot for the details.

Hi Tom,

I have the similar issue where i am suspecting that the values are in hex format.

Bind#0

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=192 off=0

kxsbbbfp=2af34b3f9bc0 bln=11 avl=11 flg=05

value=

Dump of memory from 0x00002AF34B3F9BC0 to 0x00002AF34B3F9BCB

2AF34B3F9BC0 19087178 1C010108 4B80B37D [xq......}..K]

Bind#1

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=16

kxsbbbfp=2af34b3f9bd0 bln=11 avl=11 flg=01

value=

Dump of memory from 0x00002AF34B3F9BD0 to 0x00002AF34B3F9BDB

2AF34B3F9BD0 08097178 1C010109 0080B37D [xq......}...]

Bind#2

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=32

kxsbbbfp=2af34b3f9be0 bln=11 avl=11 flg=01

value=

Dump of memory from 0x00002AF34B3F9BE0 to 0x00002AF34B3F9BEB

2AF34B3F9BE0 08097178 1C010109 0080B37D [xq......}...]

However, i tried your way(using your function) but can't get it through. Do you know how to get the timestamp value of the bind variable above?

Thanks.

I have the similar issue where i am suspecting that the values are in hex format.

Bind#0

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=192 off=0

kxsbbbfp=2af34b3f9bc0 bln=11 avl=11 flg=05

value=

Dump of memory from 0x00002AF34B3F9BC0 to 0x00002AF34B3F9BCB

2AF34B3F9BC0 19087178 1C010108 4B80B37D [xq......}..K]

Bind#1

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=16

kxsbbbfp=2af34b3f9bd0 bln=11 avl=11 flg=01

value=

Dump of memory from 0x00002AF34B3F9BD0 to 0x00002AF34B3F9BDB

2AF34B3F9BD0 08097178 1C010109 0080B37D [xq......}...]

Bind#2

oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=32

kxsbbbfp=2af34b3f9be0 bln=11 avl=11 flg=01

value=

Dump of memory from 0x00002AF34B3F9BE0 to 0x00002AF34B3F9BEB

2AF34B3F9BE0 08097178 1C010109 0080B37D [xq......}...]

However, i tried your way(using your function) but can't get it through. Do you know how to get the timestamp value of the bind variable above?

Thanks.