Yaping's Weblog

August 30, 2008

Low value/High value

Filed under: Oracle — Yaping @ 4:10 am
Tags:

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

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: