One of many useful things about SQL Monitor report is that it captures the bind variable values during the execution of the sql along with many more performance metrics from the environment it is executing.
However, the challenge was during the interpretation of bind variable values from SQL Monitor report for Timestamp datatype that will be presented something like this
================================================
| Name | Position | Type | Value |
================================================
| :B1 | 1 | TIMESTAMP | 78790604010101 |
================================================
That string is the Hex version of what you will see in a DUMP function call.
demo@XEPDB1> create table t( x timestamp );
Table created.
demo@XEPDB1> insert into t(x) values ( to_timestamp('04-Jun-2021','dd-mon-yyyy') );
1 row created.
demo@XEPDB1> commit;
Commit complete.
demo@XEPDB1> select dump(x) from t;
DUMP(X)
---------------------------------
Typ=180 Len=7: 120,121,6,4,1,1,1
demo@XEPDB1>
Converting that dump bytes to hex, we will see something like this 78,79,06,04,01,01,01
demo@XEPDB1> select to_char(120,'fmxx')||
2 to_char(121,'fmxx')||
3 to_char(6,'fm00')||
4 to_char(4,'fm00')||
5 to_char(1,'fm00')||
6 to_char(1,'fm00')||
7 to_char(1,'fm00')
8 from dual;
TO_CHAR(120,'FMXX')||
---------------------
78790604010101
Which is what appears in the sql-monitor, going back to decimals we can break this down into its bytes
- century + 100
- year + 100
- month
- day
- hour + 1
- minute + 1
- second + 1
- nanoseconds (4bytes)
- time zone hour + 20
- time zone minute + 60
So
120,121 = 2021
6 = June
4 = 4th
1 = 00am (UTC)
1 = 00 min
1 = 00 sec
So putting this entire logic into the function to return the actual data will be like this.
demo@XEPDB1> create or replace function ts_from_monitor( p_str in varchar2 )
2 return timestamp as
3 l_century number;
4 l_yr number;
5 l_month varchar2(2);
6 l_day varchar2(2);
7 l_hour varchar2(2);
8 l_min varchar2(2);
9 l_secs varchar2(2);
10 l_final varchar2(20);
11 begin
12 l_century := to_number(substr( p_str,1,2 ),'fmxx')-100 ;
13 l_yr := to_number(substr( p_str,3,2 ),'fmxx')-100 ;
14 l_month := to_char(to_number(substr( p_str,5,2 ),'fmxx'),'fm00') ;
15 l_day := to_char(to_number(substr( p_str,7,2 ),'fmxx'),'fm00') ;
16 l_hour := to_char( to_number(substr( p_str,9,2 ),'fmxx') -1 ,'fm00') ;
17 l_min := to_char( to_number(substr( p_str,11,2 ),'fmxx') -1,'fm00') ;
18 l_secs := to_char( to_number(substr( p_str,13,2 ),'fmxx') -1,'fm00') ;
19 l_final := to_number(substr( p_str,15 ),'fmxxxxxxxx');
20 return to_timestamp(
21 l_century||l_yr||l_month||l_day
22 ||l_hour||l_min||l_secs||l_final,
23 'yyyymmddhh24missff');
24 end;
25 /
Function created.
Now using this function, we can decode any bind variable value of Timestamp datatype from SQL Monitor report into actual values like this.
demo@XEPDB1> select ts_from_monitor('78790604010101') from dual;
TS_FROM_MONITOR('78790604010101')
---------------------------------------------------------------------------
04-JUN-2021 12.00.00.000000000 AM
demo@XEPDB1> select ts_from_monitor('7879021912040336E63AD0') from dual;
TS_FROM_MONITOR('7879021912040336E63AD0')
---------------------------------------------------------------------------
25-FEB-2021 05.03.02.921058000 PM
