杜芬宅 由於工作關係，會接觸很多不同的 Database
例如有 MYSQL, MS SQL Server, Oracle Database
於是今天便出大事啦
序
承上篇，這是海豚在處理在Java上用Apache POI 去讀Excel的麻煩program
發生錯誤 value too large for column
有一 application 作data insertion 到 oracle DB 時發生錯誤:
java.sql.SQLException: insertData error: java.sql.SQLException: ORA-12899: value too large for column "dbuser"."TABLE_NAME"."COLUMN_NAME" (actual: 811, maximum: 700)
釐清背後的原理
解決前需要先明白 Character Set + Encoding
Basically:
charset is the set of characters you can use
encoding is the way these characters are stored into memory
早期繁簡中文是不同的字庫 charset
又是看完也不明白的陳述，海豚將它譯成人話。
character set (簡稱 charset)，中文譯作字庫/字元集，可以理解成1組字庫，你可以使用的文字也取決於字庫是否提供這些文字。例如︰
- Big5 是早期使用的繁中字庫，收錄13,060個漢字。
- Chinese Guobiao (GB) 是簡體中文字庫
- ASCII 主要是英文字母(大寫、小寫) + 一些標點符號
Encoding ，中文譯作編碼，是指字元的儲存樣式。簡單地理解，電腦儲存使用二進制(Binary) 0101。
例如 ASCII 中的A 以二進制(Binary)儲存是 0100 0001，以十進制(Decimal)儲存是 65，以十六進制(Hexadecimal)是41。
但在其它字庫 0100 0001 很有機會是代表其它字元，或在其它字庫中根本沒有對應的字元
為了方便和統一，出現了 Unicode
為了解决傳統字元編碼的侷限性 (GB 只可以顯示簡中、ASCII 只可顯示英文，一份文件不能同時使用多種 character set)，便有Unicode的誕生， Unicode 其後不斷更新，收錄了 ASCII、中文、日文、韓文……等多種文字。
在此 Unicode 是character set，UTF-8 則是Encoding
UTF-8 Encoding
電腦以8bit(0/1) 為1byte(0100 0001)，UTF-8是指以8 bit (1 Byte)為單位去儲存文字。
而UTF-8 儲存文字是 由 1-4 Byte 組成。
當然你很快變會發現，Unicode 理論上可以儲存2^(4*8) = 4294967296個字元
但，錯，按照
RFC 3629 對 UTF-8 的規範
當1組byte時，首bit 為 higher-order bit 必定是0，其餘7 bits用作 value
當N(2-4)組byte時，首N個bit為higher-order bit 必作1，緊接0，首byte的其它bit代表的是 value。而接下來的byte首兩個是higher-order bit 必作10，其餘bit代表的是 value
因此最多可顯示字元為 2^21 (21個x，x可作0或1) = 2,097,152 characters
然後你發現了嗎，那個設計很智慧地加入了
何時開始 + 何時結束 indicator 在首 btye (0xxxxxxx)
何時開始 indicator 在首 btye，未結束的 indicator 則是 (10xxxxxx)
這同時達成了 相容 ASCII 的效果，由於 ASCII 只有128字元，即是 2^7 使用7個bit剛剛好
換句話，將一份ASCII的文件，轉換成UTF-8，沒有字元會對應不上，
無痛轉換，發明這個真天才
接下來出現的 UTF-16, UTF-32 只是儲存形色不同，但都是對應 Unicode Character Set
UTF-16 儲存1字元(character)由 2-4 Byte(s) 組成
UTF-32 儲存1字元(character)固定使用 4 Byte(s)
找到出錯的原因
問題的成因是由於 insert statement 中包含 ASCII 以外的文字，
雖然program有讀取 DB 欄位的長度上限，用作檢查輸入的文字是否超過長度
但是不知道是誰當初設計時使用了 VARCHAR2(50 BYTE)
而要insert value 是
>>>
2.SP-55-171-1000 ซีลแกนเทฟล่อน =1 ชุด
<<<
UTF-8的value要放進ASCII 的存儲空間
洞不夠大當然插不進去啦
文字上是 50個字元內，相信檔案使用了 Unicode ，因為當中保存了 ASCII 以外的的字元
文字長度 已經超過了 50 BYTE，所以才會報錯誤。
解決方法很簡單，只需改成 50 CHAR 便可
解決
檢查 Oracle DB 現在使用的 Character Set
SELECT * FROM NLS_DATABASE_PARAMETERS;
例句
ALTER TABLE table_name
MODIFY column_name action;
實作
ALTER TABLE accounts
MODIFY description varchar2( 4000 CHAR ) NOT NULL;
Reference
SQL Error: ORA-12899: value too large for column
https://stackoverflow.com/questions/4347888/sql-error-ora-12899-value-too-large-for-column
What's the difference between encoding and charset?
https://stackoverflow.com/questions/2281646/whats-the-difference-between-encoding-and-charset
ASCII Table
https://www.cs.cmu.edu/~pattis/15-1XX/common/handouts/ascii.html
Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion
utf 8 - How many characters can UTF-8 encode? - Stack Overflow
https://stackoverflow.com/questions/10229156/how-many-characters-can-utf-8-encode
UTF-8 definition
https://tools.ietf.org/html/rfc3629#section-3
Difference between BYTE and CHAR in column datatypes
https://stackoverflow.com/questions/81448/difference-between-byte-and-char-in-column-datatypes
Oracle VARCHAR2
https://www.oracletutorial.com/oracle-basics/oracle-varchar2/
difference between varchar2(4000 byte) & varchar2(4000 char)
No comments:
Post a Comment