How to convert internal format value to visible value about low value/high value in *_tab_columns.
There is a procedure named convert_raw_value in dbms_stats to achieve it.
SQL> create table t1 as select object_id,object_name,created from all_objects;
Table created.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> l
1* select COLUMN_NAME,DATA_TYPE,LOW_VALUE,HIGH_VALUE from user_tab_columns
SQL> /
COLUMN_NAME DATA_TYPE LOW_VALUE HIGH_VALUE
—————————— ——————– —————————— —————————————-
OBJECT_ID NUMBER C20317 C23D26
OBJECT_NAME VARCHAR2 414747584D4C494D50 5F414C4C5F52455053495445535F4E4557
CREATED DATE 786B051B0F1505 786B0719182E2D
SQL> create or replace function convert_raw_value(p_rawval in raw,p_type in varchar2 default ‘VARCHAR2′)
2 return varchar2
3 as
4 v_type varchar2(20) := p_type;
5 v_res_char varchar2(50);
6 v_res_date date;
7 v_res_number number;
8 v_res_val varchar2(50);
9 begin
10 if v_type = ‘VARCHAR2′ or v_type is null then
11 dbms_stats.convert_raw_value(p_rawval,v_res_char);
12 v_res_val := v_res_char;
13 end if;
14
15 if v_type = ‘NUMBER’ then
16 dbms_stats.convert_raw_value(p_rawval,v_res_number);
17 v_res_val := to_char(v_res_number);
18 end if;
19
20 if v_type = ‘DATE’ then
21 dbms_stats.convert_raw_value(p_rawval,v_res_date);
22 v_res_val := to_char(v_res_date,’yyyy/mm/dd hh24:mi:ss’);
23 end if;
24
25 return v_res_val;
26
27 exception
28 when others then
29 return ‘ERROR’;
30 end;
31 /
Function created.
SQL> select COLUMN_NAME,convert_raw_value(LOW_VALUE,DATA_TYPE) LOW_VALUE,convert_raw_value(HIGH_VALUE,DATA_TYPE) HIGH_VALUE from user_tab_columns;
COLUMN_NAME LOW_VALUE HIGH_VALUE
—————————— —————————— ——————————
OBJECT_ID 222 6037
OBJECT_NAME AGGXMLIMP _ALL_REPSITES_NEW
CREATED 2007/05/27 14:20:04 2007/07/25 23:45:44
————————————
Manual convert
C20317 => 0x C2 03 17 => (193+1) 03 23 => (03–1)* 100 * 1 + (23–1)= 222
414747584D4C494D50 => 0x 41 47 47 58 4D 4C 49 4D 50 => 65 71 71 88 77 76 73 77 80 => A G G X M L I M P
786B051B0F1505 => 0x 78 6B 05 1B 0F 15 05 => 120 107 05 27 15 21 05 => (120–100) (107-100) 05 27 (15-1) (21-1) (05-1) => 2007/05/27 14:20:04
联系客服