Java. SQL. Batch Update Exception: ORA-12899

Java. SQL. Batch Update Exception: ORA-12899

Recently, when I used the JDOM component to parse XML file data and store the data to the oracle database, the following error occurs:

Exception in thread "main" java.sql.BatchUpdateException: ORA-12899: value too large for column "SCOTT"."EMP1"."JOB" (actual: 12, maximum: 9)at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:8738)at com.uestc.util.InsertEmp.main(InsertEmp.java:48)

The XML file to be parsed is as follows:

 
 
  
   
    
1000
   
   
    
Zhuge Liang
   
   
    
Manager
   
   
    
1998-09-19
   
   
    
3000
   
   
    
500
   
  
  
   
    
1001
   
   
    
Guan Yunchang
   
   
    
Manager
   
   
    
2017-03-12
   
   
    
3500
   
   
    
200
   
  
  
   
    
1002
   
   
    
Zhao Zilong
   
   
    
Project Manager
   
   
    
2000-07-27
   
   
    
5000
   
   
    
1600
   
  
  
   
    
1003
   
   
    
Liu Xuande
   
   
    
Personnel
   
   
    
2001-0
   
   
    
2000
   
   
    
1300
   
  
 

The emp1 table has been created in the oracle database in advance. The Script Creation statement is:

CREATE TABLE emp1(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2));

The cause is that the oracle Chinese character set encoding is insufficient.

View the character set of the lifecycle CLE server, and enter the following query statement:

Select userenv ('language') from dual;

If the following content is displayed:

 

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Each character in oracle occupies two bytes.

If the following content is displayed:

SIMPLIFIED CHINESE_CHINA.AL32UTF8

Each character in oracle occupies three bytes.

The local database code queried is AL32UTF8. Each Chinese Character occupies 3 bytes. Therefore, the "Project Manager" occupies 12 bytes in the job, the created database table is allocated with only nine bytes, so this exception is thrown. Change the table creation script as follows to solve the problem:

CREATE TABLE emp1(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(12),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2));