oracle库中所有表一次性转hive表结构脚本

    SELECT
    --当出现ORA-01489:
    --     TO_CHAR(SUBSTR(table_prefix || col_strs || table_subfix, 1, 4000)) con
    (table_prefix || replace(RTRIM (col_strs,','),chr(34), chr(39))|| RTRIM (table_subfix,';') ||'ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\036'' STORED AS PARQUET;' ) con
    --     xmlagg(xmlparse(content  table_prefix  || col_strs || table_subfix)  order by table_prefix).getclobval()  con
    FROM
    (
    SELECT
    n.table_prefix,
    --WM_CONCAT(m.col_str) AS col_strs,
    --当出现ORA-01489: 字符串连接的结果过长错误时使用xmlagg + xmlparse语法解决
    --         LISTAGG(m.col_str,','  )  within group(order by n.table_prefix) as col_strs,
    xmlagg(xmlparse(content m.col_str||',') order by m.SEGMENT_COLUMN_ID).getclobval() as col_strs,
    n.table_subfix
    FROM
    (
    SELECT
    a.table_name,
    -- 修改为先删除再创建
    --             'create table if not exists ' || UPPER(a.table_name) || '(' AS table_prefix,
    ('DROP TABLE IF EXISTS ' || UPPER(a.table_name) ||';' || 'CREATE TABLE ' || UPPER(a.table_name) || '(') AS table_prefix,
    ') comment ''' || b.comments || ''';' AS table_subfix
    FROM
    user_tables a,
    user_tab_comments b
    WHERE
    a.table_name = b.table_name
    ORDER BY
    a.table_name) n,
    (
    SELECT
    c.TABLE_NAME,
    c.SEGMENT_COLUMN_ID,
    c.column_name ||
    CASE
    WHEN c.DATA_TYPE IN ('CHAR',
    'NCHAR',
    'VARCHAR',
    'VARCHAR2',
    'NVARCHAR2',
    'DATE',
    'TIMESTAMP',
    'TIMESTAMP WITH TIME ZONE',
    'TIMESTAMP WITH LOCAL TIME ZONE',
    'INTERVAL YEAR TO MOTH',
    'INTERVAL DAY TO SECOND',
    'BLOB',
    'CLOB',
    'NCLOB',
    'BFILE',
    'RAW',
    'LONG RAW') THEN ' STRING '
    WHEN C.DATA_TYPE = 'INTEGER' THEN ' BIGINT '
    WHEN C.DATA_TYPE = 'NUMBER' THEN (
    CASE WHEN C.DATA_SCALE IS NOT NULL
    AND c.DATA_SCALE <> 0 THEN ' DECIMAL(' || C.DATA_PRECISION || ',' || C.DATA_SCALE || ') '
    WHEN C.DATA_PRECISION < 3 THEN ' TINYINT '
    WHEN C.DATA_PRECISION < 5 THEN ' SMALLINT '
    WHEN C.DATA_PRECISION < 10 THEN ' INT '
    ELSE ' BIGINT '
    END)
    WHEN C.DATA_TYPE IN ('BINARY_FLOAT',
    'BINARY_DOUBLE',
    'FLOAT') THEN ' DOUBLE '
    ELSE ' STRING '
    END || 'comment ''' || REGEXP_REPLACE(T.comments, '[' || CHR(10) || CHR(13) || CHR(9) || CHR(32) || ']', '') || '''' AS col_str
    FROM
    user_tab_cols c,
    user_col_comments t
    WHERE
    c.TABLE_NAME = t.table_name
    AND c.COLUMN_NAME = t.column_name
    ORDER BY c.SEGMENT_COLUMN_ID
    ) m
    WHERE
    n.table_name = m.table_name
    GROUP BY
    n.table_prefix,
    n.table_subfix
    );