Skip to Main Content
  • Questions
  • How to find a value of timestamp bind variable in a trace file

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Paul.

Asked: December 19, 2007 - 12:22 pm UTC

Last updated: December 19, 2007 - 2:09 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

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.


and we said...

say you have:

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

  (2 ratings)

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

Comments

Paul Druker, December 19, 2007 - 4:48 pm UTC

Great answer! Thanks a lot for the details.

Eddy Ng, September 10, 2013 - 3:52 am UTC

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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library