oracle库中选择性把表生成hive创表脚本

公司要搭建数据仓库，把oracle的数据导出到hive，首先要生成创表语句
参考了https://blog.csdn.net/ZhouyuanLinli/article/details/100096647这篇文章，对代码增加了功能，可以选择性把表生成创表语句。

select to_char(substr(table_prefix || col_strs || table_subfix, 1, 4000)) con
from (
    select n.table_prefix,
           wm_concat(m.col_str) as col_strs,
           n.table_subfix
          from (
                    select a.table_name,
                           'create table if not exists ' || lower(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.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
                    and c.TABLE_NAME in ('T_PREM_LIMIT', 'T_BENEFIT_AGENT', 'T_CASH')--把这行注销可以把所有的表生成hive语句
                ) m
                where n.table_name = m.table_name
                group by n.table_prefix, n.table_subfix
)