oracl 資料庫 sqlplus 匯出資料為sql檔案

属于我的梦，明明还在發表於2024-10-24
資料庫SQL

首先確定表資料

然後執行匯出命令

SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET LINESIZE 32767
SET FEEDBACK 6

SPOOL BPM_EXT_PROCINST.sql

SELECT 'INSERT INTO BPM_EXT_PROCINST (ID, BUSI_ID, BUSI_NAME, BUSI_TYPE_ID, BUSI_TYPE_NAME, START_TIME, END_TIME, PROCDEP_ID, J4_PI_ID, J4_PI_DBID, J4_PI_STATE) VALUES (' ||
        TO_CHAR(ID) || ', ''' ||
        REPLACE(BUSI_ID, '''', '''''') || ''', ''' ||
        REPLACE(BUSI_NAME, '''', '''''') || ''', ''' ||
        REPLACE(BUSI_TYPE_ID, '''', '''''') || ''', ''' ||
        REPLACE(BUSI_TYPE_NAME, '''', '''''') || ''', ''' ||
        TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS') || ', ' ||
        TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS') || ', ''' ||
        REPLACE(PROCDEP_ID, '''', '''''') || ''', ''' ||
        REPLACE(J4_PI_ID, '''', '''''') || ''', ''' ||
        REPLACE(J4_PI_DBID, '''', '''''') || ''', ''' ||
        REPLACE(J4_PI_STATE, '''', '''''') || ''');' AS insert_statement
FROM BPM_EXT_PROCINST;

SPOOL OFF

涉及中文，新增中文環境變數

export NLS_LANG=.AL32UTF8
sqlplus username/password@database

相關文章