column low/high values - adding timestamp

Previous post:
https://ora-00932.blogspot.com/2017/09/utlrawcasttodate-column-lowhigh-values.html

Reference:

        https://jonathanlewis.wordpress.com/2020/04/10/raw-timestamp/

In recent versions of Oracle (18c onwards) the package has finally acquired several functions to do a proper job, these are:

• convert_raw_to_bin_double()
• convert_raw_to_bin_float()
• convert_raw_to_date()
• convert_raw_to_number()
• convert_raw_to_nvarchar()
• convert_raw_to_rowid()
• convert_raw_to_varchar()

The task of interrogating high/low values for columns is easier, but still doesn't include timestamps.

Like the previous post, I won't use all of these "new" features, I'll update my script that works from 11g onwards.

Realistically, I don't need sub-second precision on stats high/low value.
Turns out the date code works just fine to convert the first 7 bytes into a date.

To get the rest of the information - follow this post 

https://clarodba.wordpress.com/2021/11/04/decode-high_value-and-low_value-including-timestamp-data-types/

  

select 
column_name, data_type, data_length, num_distinct, num_nulls, nullable,
case 
  when data_type in( 'VARCHAR2' ,'VARCHAR','CHAR')  then substr(utl_raw.cast_to_varchar2(low_value), 1, 30)
  when data_type in('FLOAT', 'NUMBER')  then to_char(utl_raw.cast_to_number(low_value), 'FM99999999999990D9999')
  when data_type = 'DATE' or data_type like 'TIMESTAMP%' then to_char( to_date(( 
     select listagg(to_char(
          to_number(  regexp_substr( dump(low_value) , '[0-9]+', 1, level+2 )  , '999')
          - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
       , 'FM999900')) within group( order by level  )
      from dual connect by rownum <= 7 
    ) , 'YYYYMMDDHH24MISS'), 'DD-Mon-YYYY HH24:MI:SS' )
  else 'NOT SUPPORTED'
end low_value ,
case 
  when data_type in( 'VARCHAR2' ,'VARCHAR','CHAR')  then substr(utl_raw.cast_to_varchar2(high_value), 1, 30)
  when data_type in('FLOAT', 'NUMBER')  then to_char(utl_raw.cast_to_number(high_value), 'FM99999999999990D9999')
  when data_type = 'DATE' or data_type like 'TIMESTAMP%'  then to_char( to_date(( 
     select listagg(to_char(
          to_number(  regexp_substr( dump(high_value) , '[0-9]+', 1, level+2 )  , '999')
          - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
       , 'FM00')) within group( order by level  )
      from dual connect by rownum <= 7 
    ) , 'YYYYMMDDHH24MISS'), 'DD-Mon-YYYY HH24:MI:SS' )
  else 'NOT SUPPORTED'
end high_value 
from all_tab_cols
where owner = '&OWNER.'
and table_name = '&TABLE.'

This method also works for the hex dump bind values captured in ASH data.