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.