sqlplus執行sql檔案報錯

一：問題描述：今天別的公司給的sql檔案（201901.sql），內容如下：Insert into T_ORDER (ORDER_NO,ORG_ORDER_NO,MCHNT_NO,MCHNT_NAME,ORG_NAME,ORG_ID,MD,ORDER_AMOUNT,AMOUNT,CREATED,SOURCE,PAY_ST,TIME_PAID,OPEN_ID) values ('15463005906703193844','15463005906703193844','193298','王學政源','支行','005265','轉入',200000,200000,to_timestamp('01-1月 -19 07.56.30.670000000 上午','DD-MON-RR HH.MI.SSXFF AM'),0,2,to_timestamp('01-1月 -19 07.56.13.000000000 上午','DD-MON-RR HH.MI.SSXFF AM'),'694BED617AEA24F48F8E564393AFE851');

資料量大概是1000多萬，所以採用批次執行sql檔案來匯入資料庫，但是報錯,兩個問題！

SQL>@201901.sql

問題1，具體報錯如下，這個問題是字符集的問題，

ORA-01756: quoted string not properly terminated

問題2 ，具體報錯如下，這個問題是日期格式引數的問題

ORA-01843: not a valid month

然而用plssql developer工具執行一條資料，是可以成功的，這說明資料本身是沒有問題，plssql developer強大之處在於它可以作為資料庫和客戶端的無縫相容，格式自動轉換，字符集以及其他的格式轉換

二：問題解決：

一：解決字符集問題：

1.1.file -i 看檔案的字符集，發現是utf-8

[oracle@ory-mysql-east-01 backup]$ file -i 2019012.sql

2019012.sql: text/plain; charset=utf-8

1.2.然後設定oracle使用者的環境變數NLS_LANG(保持和檔案的字符集一樣）

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
二：解決日期格式引數的問題

因為sql檔案中的日期是中文的，例如：01-1月 -19 07.56.13.000000000 上午，但是資料庫的日期格式是英文的，

如下所示，所以只需要修改下日期的格式即可，NLS_DATE_LANGUAGE設定為簡體中文，為防止系統出現問題，只需要修改session級別的即可！

2.1.剛開始 是英文的如下所示：

SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';

PARAMETER                             VALUE

--------------------------------------------------------------------------------

NLS_DATE_LANGUAGE           AMERICAN

SQL> select sysdate from dual;

SYSDATE

------------------

30-AUG-19

2.2修改為簡體中文的：

SQL> alter session set nls_date_language='SIMPLIFIED CHINESE';

Session altered.

SQL> select sysdate from dual;

SYSDATE

--------------

30-8月 -19

SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';

PARAMETER                             VALUE

--------------------------------------------------------------------------------

NLS_DATE_LANGUAGE           SIMPLIFIED CHINESE
三：問題分析：

一：關於字符集：oracle在處理資料的時候，簡單分成兩類 insert和select

insert 的過程：首先以Oracle客戶端的字符集(lang)編碼------->然後透過客戶端nls_lang告訴sqlplus是以那種字符集編碼的------->然後Oracle資料庫得知nls_lang後，藉助自己的編碼表來轉換成資料庫的字符集(注意資料庫的字符集可以和nls_lang不一樣！)，如果使用lang=utf8編碼，然後nls_lang=gbk，等Oracle轉換的時候，透過gbk的編碼表來轉換，那麼肯定會亂碼的！！！！！！！所以 只需要lang和nls_lang一樣，那麼insert就不會亂碼！！！！不管資料庫的字符集和他一樣不一樣！！！也就是說Oracle會做字符集轉換的工作，客戶端不會轉換，只會根據lang來查詢對應的字串， 然後如果是執行一個檔案，那麼只需要保證NLS_LANG設定的和檔案的字符集一樣即可！

select的過程：Oracle資料庫根據資料庫的字符集utf8把查詢的內容進行編碼--------->然後告訴sqlplus程式我查出來的內容是以utf8進行編碼的，然後sqlplus告訴Oracle他們需要的是nls_lang=gbk，於是Oracle發現不一樣，這樣Oracle就根據自己的編碼表來找gbk的對應編碼，然後傳給sqlplus程式------>sqlplus直接把得到的這串編碼扔給客戶端作業系統，而作業系統只有ZHS16GBK編碼表，它不會問得到的這串編碼是什麼格式的! 所以當你使用sqlplus / as sysdba連線資料庫查詢的時候， 你只需要保證你的NLS_LANG設定的和你的工具（例如CRT或者xshell）字符集一樣即可！，不需要保證NLS_LANG和你的資料庫一樣（oracle會自動轉換）
二：關於 日期格式引數nls_date_language的知識

SQL> show parameter nls_date_language;
NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_language                    string
沒有設定，是預設值，參看文件：預設值取自於 NLS_LANGUAGE

Property	Description
Parameter type	String
Syntax	NLS_DATE_LANGUAGE = language
Default value	Derived from NLS_LANGUAGE
Modifiable	ALTER SESSION
Range of values	Any valid NLS_LANGUAGE value

然後檢視nls_language發現是英文的！SQL> show parameter nls_language;
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------nls_language                         string      AMERICAN當真正出現問題的時候，你只需要關注session級別的nls_date_language引數的值即可：如下檢視

SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';

PARAMETER                             VALUE

--------------------------------------------------------------------------------

NLS_DATE_LANGUAGE           SIMPLIFIED CHINESE

保證你的session級別的nls_date_languag是你想要的值，就沒沒問題！

總結：出現問題了不要去找資料的問題，因為這麼大的資料量，一般都是批次匯出的，更何況透過plsql developer工具執行一天資料可以成功，一般不會出現問題的，只可能是資料庫的配置不一樣導致。