low_value / high_value

If you take a look at view user_tab_columns (or equivalent) you will find that its definition includes columns low_value and high_value, which give the currently known low and high value for the data. For example:

 
select 
	column_name, low_value, high_value 
from 
	user_tab_columns 
where 
	table_name  = 'T1' 
and	column_name = 'SKEW' 
;         

COLUMN_NAME          LOW_VALUE       HIGH_VALUE 
-------------------- --------------- --------------- 
SKEW                 C102            C151       

This is fine, of course, if you are good at translating internal forms of data into human-readable forms.

For those of us less gifted, the dbms_stats package gives us some help with a set of procedures with names like convert_raw_value(). There are three overloaded functions to handle varchar2, date, and number types (with binary_float and binary_double appearing as new options in 10g), and two special cases: convert_raw_value_nvarchar() and convert_raw_value_rowid(). The procedures all take two parameters – the first in an input which is the raw value to be converted, the second is an output which has to be a variable of the correct type, so, for example:

 
SQL> declare 
  2     m_n     number; 
  3  begin 
  4     dbms_stats.convert_raw_value('C102',m_n); 
  5     dbms_output.put_line('Result: ' || m_n); 
  6  end; 
  7  . 
SQL> / 
Result: 1        

For convenience, you can wrap calls to the procedures inside functions, which then makes them available for use in SQL.

The following example is just to get you started (the column called skew is one I know to be a number type) – you would probably want to create a package to hold several different functions, and declare the functions as deterministic.

create or replace function raw_to_num(i_raw raw) 
return number 
as 
	m_n number; 
begin 
	dbms_stats.convert_raw_value(i_raw,m_n); 
	return m_n; 
end; 
/     

select 
	column_name, 
	raw_to_num(low_value)		low_val, 
	raw_to_num(high_value)		high_val 
from 
	user_tab_columns 
where 
	table_name  = 'T1' 
and	column_name = 'SKEW' 
;     

COLUMN_NAME             LOW_VAL   HIGH_VAL 
-------------------- ---------- ---------- 
SKEW                          1         80     

To finish off, you would probably want to query the user_tab_columns (or related) views with a decode() on the data_type to decide which of the functions to use, and then perform an explicit to_char() on the result of the decode(). But that task is left as an exercise for the reader.

Addendum: If you read the comments you will see that some of them have taken up the exercise and supplied some answers.

Update Apr 2020

Oracle has finally added the functions to dbms_stats that I’ve indicated in the notes above; so in 19c (and probably in 18c) the following functions are available:

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_varchar2 

THese all take a RAW as an input (which you might generate using the hextoraw() function, and return the type indicated by the function name.