RAW 변환 함수

DBA_TAB_COLUMNS 뷰의 low_value, high_value 열은 값이 RAW 타입으로 저장되어 있다. RAW 값을 VARCHAR2 값으로 변환하는 함수를 작성해보자.

아래와 같이 함수를 작성하자. 값 변환을 위해 UTL_RAW, DBMS_STATS 패키지를 사용했다. TIMESTAMP 타입은 변환 함수가 없어 직접 디코딩했다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

-- 1 CREATE OR REPLACE FUNCTION fnc_raw (i_typ IN VARCHAR2, i_val IN RAW) RETURN VARCHAR2 IS     FUNCTION fnc_datetime (i_val IN RAW) RETURN VARCHAR2     IS     BEGIN         RETURN             RTRIM (          LPAD (100                                  * TO_NUMBER (SUBSTR (i_val,  1,  2), 'XX')                                  + TO_NUMBER (SUBSTR (i_val,  3,  2), 'XX')                                  - 10100                                      , 4, '0')                    || '-' || LPAD (TO_NUMBER (SUBSTR (i_val,  5,  2), 'XX')   , 2, '0')                    || '-' || LPAD (TO_NUMBER (SUBSTR (i_val,  7,  2), 'XX')   , 2, '0')                    || ' ' || LPAD (TO_NUMBER (SUBSTR (i_val,  9,  2), 'XX') -1, 2, '0')                    || ':' || LPAD (TO_NUMBER (SUBSTR (i_val, 11,  2), 'XX') -1, 2, '0')                    || ':' || LPAD (TO_NUMBER (SUBSTR (i_val, 13,  2), 'XX') -1, 2, '0')                    || '.' || LPAD (TO_NUMBER (SUBSTR (i_val, 15, 18), 'XX') -1, 8, '0'), '.');     END fnc_datetime;       FUNCTION fnc_rowid (i_val IN RAW) RETURN VARCHAR2     IS         l_val    ROWID;     BEGIN         DBMS_STATS.CONVERT_RAW_VALUE_ROWID (i_val, l_val);         RETURN ROWIDTOCHAR (l_val);     END fnc_rowid; BEGIN     RETURN         CASE             WHEN i_val IS NULL           THEN NULL             WHEN i_typ = 'CHAR'          THEN UTL_RAW.CAST_TO_VARCHAR2 (i_val)             WHEN i_typ = 'VARCHAR2'      THEN UTL_RAW.CAST_TO_VARCHAR2 (i_val)             WHEN i_typ = 'NVARCHAR2'     THEN TO_CHAR (UTL_RAW.CAST_TO_NVARCHAR2 (i_val))             WHEN i_typ = 'NUMBER'        THEN TO_CHAR (UTL_RAW.CAST_TO_NUMBER (i_val))             WHEN i_typ = 'BINARY_DOUBLE' THEN TO_CHAR (UTL_RAW.CAST_TO_BINARY_DOUBLE (i_val))             WHEN i_typ = 'BINARY_FLOAT'  THEN TO_CHAR (UTL_RAW.CAST_TO_BINARY_FLOAT (i_val))             WHEN i_typ = 'DATE'          THEN fnc_datetime (i_val)             WHEN i_typ LIKE 'TIMESTAMP%' THEN fnc_datetime (i_val)             WHEN i_typ = 'ROWID'         THEN fnc_rowid (i_val)             ELSE NULL         END; EXCEPTION     WHEN OTHERS THEN RETURN NULL; END; /

생성한 함수를 아래와 같이 사용할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- 2 SELECT   column_name, data_type, low_value, fnc_raw (data_type, low_value) AS low_value_vc     FROM dba_tab_columns    WHERE owner = 'SCOTT'      AND table_name = 'EMP' ORDER BY column_id;   COLUMN_NAME DATA_TYPE LOW_VALUE      LOW_VALUE_VC ----------- --------- -------------- ------------------- EMPNO       NUMBER    C24A46         7369 ENAME       VARCHAR2  4144414D53     ADAMS JOB         VARCHAR2  414E414C595354 ANALYST MGR         NUMBER    C24C43         7566 HIREDATE    DATE      77B40C11010101 1980-12-17 00:00:00 SAL         NUMBER    C209           800 COMM        NUMBER    80             0 DEPTNO      NUMBER    C10B           10   8 행이 선택되었습니다.