-- 替换 :table_name 为实际表名（大写）
WITH cols AS (
    SELECT 
        c.column_name,
        c.data_type,
        cm.comments AS column_comment,
        c.column_id,
        CASE 
            WHEN c.data_type IN ('VARCHAR2','CHAR') THEN ''''''
            WHEN c.data_type IN ('NUMBER','INTEGER') THEN '0'
            WHEN c.data_type IN ('DATE','TIMESTAMP') THEN 'SYSDATE'
            ELSE 'NULL'
        END AS default_val
    FROM all_tab_columns c
    LEFT JOIN all_col_comments cm 
        ON c.owner = cm.owner 
        AND c.table_name = cm.table_name 
        AND c.column_name = cm.column_name
    WHERE c.owner = CASE WHEN INSTR('&table_name','.')>0 
                         THEN SUBSTR('&table_name',1,INSTR('&table_name','.')-1)
                         ELSE USER END
      AND c.table_name = CASE WHEN INSTR('&table_name','.')>0 
                              THEN SUBSTR('&table_name',INSTR('&table_name','.')+1)
                              ELSE '&table_name' END
    ORDER BY c.column_id
),
col_with_comma AS (
    SELECT 
        c.*,
        CASE 
            WHEN ROW_NUMBER() OVER (ORDER BY column_id) < COUNT(*) OVER () 
            THEN ' --' 
            ELSE '' 
        END AS trailing_comma
    FROM cols c
)
SELECT 
    'INSERT INTO &table_name (' || CHR(10) ||
    LISTAGG(
        RPAD(column_name||',', 30) || 
        CASE WHEN column_comment IS NOT NULL 
             THEN ' -- ' || l 
             ELSE '' 
        END || trailing_comma || CHR(10),
        ''
    ) WITHIN GROUP (ORDER BY column_id) ||
    ')' || CHR(10) ||
    'SELECT ' || CHR(10) ||
    LISTAGG(
        RPAD(default_val || ' AS ' || column_name||',', 40) ||
        CASE WHEN column_comment IS NOT NULL 
             THEN ' -- ' || column_comment 
             ELSE '' 
        END || trailing_comma || CHR(10),
        ''
    ) WITHIN GROUP (ORDER BY column_id) ||
    'FROM DUAL;' AS generate_sql
FROM col_with_comma;

对于大表用下面这个

-- 替换 :table_name 为实际表名（大写）
WITH cols AS (
    SELECT 
        c.column_name,
        c.data_type,
        cm.comments AS column_comment,
        c.column_id,
        CASE 
            WHEN c.data_type IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR') THEN 'NULL'
            WHEN c.data_type IN ('NUMBER','INTEGER','FLOAT') THEN '0'
            WHEN c.data_type IN ('DATE','TIMESTAMP') THEN 'SYSDATE'
            ELSE 'NULL'
        END AS default_val
    FROM all_tab_columns c
    LEFT JOIN all_col_comments cm 
        ON c.owner = cm.owner 
        AND c.table_name = cm.table_name 
        AND c.column_name = cm.column_name
    WHERE c.owner = CASE WHEN INSTR('&table_name','.')>0 
                         THEN SUBSTR('&table_name',1,INSTR('&table_name','.')-1)
                         ELSE USER END
      AND c.table_name = CASE WHEN INSTR('&table_name','.')>0 
                              THEN SUBSTR('&table_name',INSTR('&table_name','.')+1)
                              ELSE '&table_name' END
)
SELECT 
    '-- ============================================' || CHR(10) ||
         '-- 表：&table_name' || CHR(10) ||
         '-- 生成时间：' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
         '-- ============================================' || CHR(10) || CHR(10) ||
    'INSERT INTO &table_name (' || CHR(10) ||
    XMLAGG(XMLELEMENT(e, 
        RPAD(column_name||',', 30) || 
        CASE WHEN column_comment IS NOT NULL 
             THEN ' -- ' || column_comment 
             ELSE ' --' 
        END || CHR(10)
    ) ORDER BY column_id).EXTRACT('//text()').GETCLOBVAL() ||
    ')' || CHR(10) || 'SELECT ' || CHR(10) ||
    XMLAGG(XMLELEMENT(e, 
        RPAD(default_val || ' AS ' || column_name||',', 40) ||
        CASE WHEN column_comment IS NOT NULL 
             THEN ' -- ' || column_comment 
             ELSE ' --' 
        END || CHR(10)
    ) ORDER BY column_id).EXTRACT('//text()').GETCLOBVAL() ||
    'FROM DUAL;' AS generate_sql
FROM cols;