轉換日期有點問題。

column trans_low format a20

column trans_high format a20

column data_default format a20

SELECT

--owner,

--? ?? ?? ?table_name,

column_name,

data_type,

data_length,

nullable,

num_distinct,

density,

sample_size,

CASE

WHEN data_type IN ('CHAR', 'VARCHAR2')

THEN

UTL_RAW.cast_to_varchar2 (low_value)

WHEN data_type = 'NUMBER'

THEN

TO_CHAR (UTL_RAW.cast_to_number (low_value))

WHEN data_type = 'DATE'

THEN

RTRIM (

LTRIM (

TO_CHAR (

100

* (??TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')

- 100)

+ (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100),

'0000'))

|| '-'

|| LTRIM (

TO_CHAR (TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX'),

'00'))

|| '-'

|| LTRIM (

TO_CHAR (TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX'),

'00'))

|| ' '

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1,

'00'))

|| ':'

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1,

'00'))

|| ':'

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1,

'00')))

END

trans_low,

CASE

WHEN data_type IN ('CHAR', 'VARCHAR2')

THEN

UTL_RAW.cast_to_varchar2 (high_value)

WHEN data_type = 'NUMBER'

THEN

TO_CHAR (UTL_RAW.cast_to_number (high_value))

WHEN data_type = 'DATE'

THEN

RTRIM (

LTRIM (

TO_CHAR (

100

* (??TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')

- 100)

+ (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100),

'0000'))

|| '-'

|| LTRIM (

TO_CHAR (TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX'),

'00'))

|| '-'

|| LTRIM (

TO_CHAR (TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX'),

'00'))

|| ' '

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1,

'00'))

|| ':'

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1,

'00'))

|| ':'

|| LTRIM (

TO_CHAR (

TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1,

'00')))

END

trans_high,

num_nulls,

num_buckets,

last_analyzed,

histogram,

data_default

FROM dba_tab_cols

WHERE??owner = decode('&1','',user,upper('&1'))

AND table_name = upper('&2')

AND column_name = decode('&3','',column_name,upper('&3'))

ORDER BY column_id

/

COLUMN_NAME? ?? ?? ? DATA_TYPE??DATA_LENGTH N NUM_DISTINCT? ? DENSITY SAMPLE_SIZE TRANS_LOW? ?? ?? ?? ?TRANS_HIGH? ?? ?? ?? ?NUM_NULLS NUM_BUCKETS LAST_ANALYZED? ?? ? HISTOGRAM? ?? ? DATA_DEFAULT

-------------------- ---------- ----------- - ------------ ---------- ----------- -------------------- -------------------- ---------- ----------- ------------------- --------------- --------------------

EMPNO? ?? ?? ?? ?? ? NUMBER? ?? ?? ?? ???22 N? ?? ?? ???14 .071428571? ?? ?? ? 14 7369? ?? ?? ?? ?? ???7934? ?? ?? ?? ?? ?? ?? ?? ???0? ?? ?? ???1 2014-11-04 07:37:34 NONE

ENAME? ?? ?? ?? ?? ? VARCHAR2? ?? ?? ?? ?10 Y? ?? ?? ???14 .071428571? ?? ?? ? 14 ADAMS? ?? ?? ?? ?? ? WARD? ?? ?? ?? ?? ?? ?? ?? ???0? ?? ?? ???1 2014-11-04 07:37:34 NONE

JOB? ?? ?? ?? ?? ?? ?VARCHAR2? ?? ?? ?? ? 9 Y? ?? ?? ?? ?5? ?? ?? ?.2? ?? ?? ? 14 ANALYST? ?? ?? ?? ???SALESMAN? ?? ?? ?? ?? ?? ?? ? 0? ?? ?? ???1 2014-11-04 07:37:34 NONE

MGR? ?? ?? ?? ?? ?? ?NUMBER? ?? ?? ?? ???22 Y? ?? ?? ?? ?6 .166666667? ?? ?? ? 13 7566? ?? ?? ?? ?? ???7902? ?? ?? ?? ?? ?? ?? ?? ???1? ?? ?? ???1 2014-11-04 07:37:34 NONE

HIREDATE? ?? ?? ?? ? DATE? ?? ?? ?? ?? ???7 N? ?? ?? ???13 .076923077? ?? ?? ? 14 1980-12-17 00:00:00??1987-05-23 00:00:00? ?? ?? ???0? ?? ?? ???1 2014-11-04 07:37:34 NONE

SAL? ?? ?? ?? ?? ?? ?NUMBER? ?? ?? ?? ???22 Y? ?? ?? ???12 .083333333? ?? ?? ? 14 800? ?? ?? ?? ?? ?? ?5000? ?? ?? ?? ?? ?? ?? ?? ???0? ?? ?? ???1 2014-11-04 07:37:34 NONE

COMM? ?? ?? ?? ?? ???NUMBER? ?? ?? ?? ???22 Y? ?? ?? ?? ?4? ?? ???.25? ?? ?? ???4 0? ?? ?? ?? ?? ?? ???1400? ?? ?? ?? ?? ?? ?? ?? ? 10? ?? ?? ???1 2014-11-04 07:37:34 NONE

DEPTNO? ?? ?? ?? ?? ?NUMBER? ?? ?? ?? ???22 Y? ?? ?? ?? ?3 .333333333? ?? ?? ? 14 10? ?? ?? ?? ?? ?? ? 30? ?? ?? ?? ?? ?? ?? ?? ?? ? 0? ?? ?? ???1 2014-11-04 07:37:34 NONE

SYS_NC00009$? ?? ?? ?VARCHAR2? ?? ?? ?? ?14 Y? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???NONE

9 rows selected.

總結

以上是生活随笔為你收集整理的oracle的工具cmd,数据库命令行工具DBCLI的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。