Oracle表导出insert语句的脚本

set line 32767
set heading off
set feedback off
set echo off
SET NEWPAGE none
SET TRIMSPOOL off
SET SQLBLANKLINES off
SET TRIMOUT on
SET TRIMS on
set timing off
SET VER OFF 

DEFINE V_TABLE_NAME='ST_RNFL_R'
DEFINE V_OWNER='DISASTER'

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
COLUMN MYSQL FORMAT A32767
set array 5000

SPOOL 1.SQL
SELECT 'SPOOL &V_TABLE_NAME'||'.SQL' TEXT FROM DUAL;
WITH DM_TBLS AS
 (SELECT DECODE(T.DATA_TYPE,
                'CHAR',
                '''''''''||' || T.COLUMN_NAME || '||''''''''',
                'VARCHAR2',
                '''''''''||' || T.COLUMN_NAME || '||''''''''',
                'DATE',
                '''TO_DATE(''''''||to_char(' || T.COLUMN_NAME ||
                ',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')''',
                T.COLUMN_NAME) COLUMN_NAME,
         T.COLUMN_ID,
         T.TABLE_NAME,
         T.OWNER,
         T.COLUMN_NAME COL1,
         LAG(COLUMN_ID) OVER(PARTITION BY T.TABLE_NAME ORDER BY T.COLUMN_ID) RN
    FROM DBA_TAB_COLUMNS T
   WHERE T.TABLE_NAME = UPPER('&V_TABLE_NAME')
     AND T.OWNER = UPPER('&V_OWNER'))
SELECT --T.TABLE_NAME,T.OWNER,
 'SELECT ''INSERT INTO &V_OWNER . &V_TABLE_NAME VALUES (''||' ||
 REPLACE(SUBSTRB(MAX(CHR(64 + LEVEL) || SYS_CONNECT_BY_PATH(T.COLUMN_NAME, '#')),
                 3),
         '#',
         '||'',''||') || '||'');'' TEXT FROM ' || T.OWNER || '.' || T.TABLE_NAME || ';' MYSQL
  FROM DM_TBLS T
 START WITH RN IS NULL
CONNECT BY RN = PRIOR COLUMN_ID
       AND TABLE_NAME = PRIOR TABLE_NAME
 GROUP BY T.OWNER,
          T.TABLE_NAME;
SELECT 'COMMIT;' TEXT FROM DUAL;
SPOOL OFF

@1.SQL

EXIT

 

将上述脚本保存为gen_insert.sql,然后以sqlplus user/password@tnsname,就可以轻松地将将oracle表内容导出为insert into语句，oracle执行完后，就可以在目录下找到一个以 表名.sql的文件。