原文出自：
https://www.cnblogs.com/lq147760524/p/9168223.html
oracle11g后 推荐使用listagg函数
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,
LISTAGG(m.col_str,',' ) within group(order by n.table_prefix) 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) m
WHERE
n.table_name = m.table_name
GROUP BY
n.table_prefix,
n.table_subfix);
注意：但当数据量比较大时，一般clob字段超过4000，却报ORA-01489:字符串连接的结果过长。有兴趣的小伙伴可以研究下解决办法。