set linesize 200
set pages 100
set wrap off
column table_name for a20
column column_name for a20
column data_type for a9
column high_value for a20
column low_value for a20
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
実行結果例
TABLE_NAME COLUMN_NAME DATA_TYPE LOW_VALUE HIGH_VALUE
------------------- -------------------- --------- -------------------- --------------------
EMP FIRST_NAME VARCHAR2 Adam Winston
EMP LAST_NAME VARCHAR2 Abel Zlotkey
EMPLOYEES COMMISSION_PCT NUMBER .1 .4
EMPLOYEES DEPARTMENT_ID NUMBER 10 110
EMPLOYEES EMAIL VARCHAR2 ABANDA WTAYLOR
EMPLOYEES EMPLOYEE_ID NUMBER 100 206
EMPLOYEES FIRST_NAME VARCHAR2 Adam Winston
EMPLOYEES HIRE_DATE DATE 2001-01-13 00:00:00 2008-04-21 00:00:00
EMPLOYEES JOB_ID VARCHAR2 AC_ACCOUNT ST_MAN
EMPLOYEES LAST_NAME VARCHAR2 Abel Zlotkey
EMPLOYEES MANAGER_ID NUMBER 100 205
EMPLOYEES PHONE_NUMBER VARCHAR2 011.44.1343.329268 650.509.4876
EMPLOYEES SALARY NUMBER 2100 24000
… 略
CREATE FUNCTION STATS_RAW2STRING(P_VALUE IN RAW, P_DATATYPE IN VARCHAR2)
RETURN VARCHAR2
IS
vDataType VARCHAR2(2000) := UPPER(P_DATATYPE);
vDate DATE;
vBinaryFloat BINARY_FLOAT;
vBinaryDouble BINARY_DOUBLE;
vNumber NUMBER;
vROWID ROWID;
vRet VARCHAR2(32767) := NULL;
BEGIN
IF (P_VALUE IS NOT NULL) THEN
CASE vDataType
WHEN 'DATE' THEN
dbms_stats.convert_raw_value(P_VALUE, vDate);
vRet := vDate;
WHEN 'BINARY_FLOAT' THEN
dbms_stats.convert_raw_value(P_VALUE, vBinaryFloat);
vRet := TO_CHAR(vBinaryFloat);
WHEN 'BINARY_DOUBLE' THEN
dbms_stats.convert_raw_value(P_VALUE, vBinaryDouble);
vRet := TO_CHAR(vBinaryDouble);
WHEN 'NUMBER' THEN
dbms_stats.convert_raw_value(P_VALUE, vNumber);
vRet := TO_CHAR(vNumber);
WHEN 'CHAR' THEN
dbms_stats.convert_raw_value(P_VALUE, vRet);
WHEN 'VARCHAR2' THEN
dbms_stats.convert_raw_value(P_VALUE, vRet);
WHEN 'NCHAR' THEN
vRet := TO_CHAR(UTL_RAW.CAST_TO_NVARCHAR2(P_VALUE));
WHEN 'NVARCHAR2' THEN
vRet := TO_CHAR(UTL_RAW.CAST_TO_NVARCHAR2(P_VALUE));
WHEN 'ROWID' THEN
dbms_stats.CONVERT_RAW_VALUE_ROWID(P_VALUE, vROWID);
vRet := TO_CHAR(vROWID);
ELSE
vRet := '['|| TO_CHAR(P_VALUE) ||']';
END CASE;
END IF;
RETURN vRet;
EXCEPTION
WHEN OTHERS THEN
vRet := 'ERR!['|| TO_CHAR(P_VALUE) ||']';
RETURN vRet;
END;
/