gabis
User
Junior Boarder
| Posts: 12 |   |
Karma: 0
|
|
RE: i need of data type and data length from query
|
|
Posted: 2003/04/02 16:49 |
|
|
|
|
This is longer SQL statement but it will
get you an excellent result:
SELECT
ALL_TAB_COLUMNS.COLUMN_NAME AS NAME,
ALL_TAB_COLUMNS.DATA_TYPE AS DATA_TYPE,
ALL_TAB_COLUMNS.DATA_LENGTH AS DATA_LENGTH,
ALL_TAB_COLUMNS.DATA_PRECISION AS DATA_PRECISION,
ALL_TAB_COLUMNS.DATA_SCALE AS DATA_SCALE,
DECODE( ALL_TAB_COLUMNS.DATA_TYPE, 'RAW', ALL_TAB_COLUMNS.DATA_TYPE||' ('||ALL_TAB_COLUMNS.DATA_LENGTH||')', 'CLOB', ALL_TAB_COLUMNS.DATA_TYPE, 'NCLOB', ALL_TAB_COLUMNS.DATA_TYPE, DECODE( NVL( ALL_TAB_COLUMNS.DATA_PRECISION, 0 ), 0, DECODE( NVL( ALL_TAB_COLUMNS.CHAR_COL_DECL_LENGTH, 0 ), 0, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_TYPE||' ('||ALL_TAB_COLUMNS.DATA_LENGTH||')' ), ALL_TAB_COLUMNS.DATA_TYPE||' ('||ALL_TAB_COLUMNS.DATA_PRECISION||DECODE( ALL_TAB_COLUMNS.DATA_SCALE, 0, '', NULL, '', ','||ALL_TAB_COLUMNS.DATA_SCALE )||')' ) ) AS DECODED_DATA_TYPE,
DECODE( ALL_TAB_COLUMNS.NULLABLE, 'Y', 'Yes', 'No' ) AS NULLABLE,
ALL_TAB_COLUMNS.DATA_DEFAULT AS DATA_DEFAULT,
ALL_COL_COMMENTS.COMMENTS AS COMMENTS
FROM
ALL_TAB_COLUMNS,
ALL_COL_COMMENTS
WHERE
ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME
AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME
AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER
AND ALL_TAB_COLUMNS.TABLE_NAME = :TABLE_NAME
AND ALL_TAB_COLUMNS.OWNER = :OWNER
ORDER BY
ALL_TAB_COLUMNS.COLUMN_ID
|
|
