dbaontap

WITH COLUMN_DEFINITION AS (
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CASE
        WHEN (DATA_TYPE= 'NUMBER' AND DATA_SCALE = 0 AND DATA_PRECISION <= 9) THEN 'INTEGER'
        WHEN (DATA_TYPE= 'NUMBER' AND DATA_SCALE = 0 AND DATA_PRECISION <= 18) THEN 'BIGINT'
        WHEN (DATA_TYPE= 'NUMBER' AND DATA_SCALE = 0 AND DATA_PRECISION >= 19) THEN 'DECIMAL(' || DATA_PRECISION || ',0)'
        WHEN (DATA_TYPE= 'NUMBER' AND DATA_SCALE > 0)  THEN 'DECIMAL(' || DATA_PRECISION || ',' || DATA_SCALE ||')'
        WHEN (DATA_TYPE= 'NUMBER' AND nvl(DATA_SCALE,0) = 0 AND nvl(DATA_PRECISION,0) = 0) THEN 'DECIMAL(38,18)'