Questions about Oracle BULK INSERT data

To intercept some log information:

2015-09-01 14:48:47,132 INFO [Org.springframework.beans.factory.xml.XmlBeanDefinitionReader]-Loading XML bean Definitions from class path resource [Org/springframework/jdbc/support/sql-error-codes.xml]
2015-09-01 14:48:47,178 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]-Sqlerrorcodes loaded: [DB2, Derby, H2, HSQL, Informix, Ms-sql, MySQL, Oracle, PostgreSQL, Sybase, Hana]
Org.springframework.jdbc.UncategorizedSQLException:
# # # Error updating database. Cause:java.sql.SQLException: ORA-01461: can only bind Long value to insert long column

Find Reason:

Report exception Reason: When the Insert log data operation to the database, the User_agent field value is too long, if it is varchar2 type, VARCHAR2 type maximum length is 4000, when more than 4000,oracle will automatically convert the field value to long type, and then, The insert operation failed. The workaround is to change the type of this field to CLOB or blob type;

Here's the workaround "because there is data in this table, a new column is added to replicate the data":

--Add a Clob type field, Clob do not need to specify the length
ALTER TABLE log_access Add (Useragent_bak clob);

--Copy the original value to the newly created field

Update Log_access a set a.useragent_bak = A.user_agent;

--Delete the original field
ALTER TABLE log_access drop column Useragent_bak

--Modify the new Clob field to the original field name
ALTER TABLE log_access Rename column Useragent_bak to User_agent

Commit

Always consider the new field, modify the field type, in fact, the inserted data is log information, some data can be intercepted in the background, so you do not have to modify the field type.

See the cause of the problem on the Internet, collected as follows:

Error "ORA-01461: You can only assign a long value for inserting a long column", there are several possible reasons for this:
1. Insert to a string longer than 4000 bytes.
2. The actual length of a field data for a record inserted into a table is greater than 2000 bytes (or 1333 bytes if UTF-8), or if there are two or more strings in the inserted record that are longer than 2000 bytes in length.
3. The database does not match the client's JDBC driver.
For some character sets in UTF-8 or Europe, Oracle stores 2 or 3 bytes of storage for one character, although the table definition is VARCHAR2 (4000), but the field is data_length twice times or 3 times times longer. In this case, Oracle will treat the data_length length of more than 4000 as long, your table has two such fields, the insertion of data is equivalent to the simultaneous operation of 2 long fields.

The difference between CLOB and blobs:

1.BLOB

BLOBs are all called binary Large objects (binary Large object). It is used to store large binary objects in the database. The maximum size that can be stored is 4G bytes

2.CLOB
Clob are all called character large objects (Character Large object). It is similar to the Long data type except that CLOB is used to store large single-byte character blocks in the database and does not support character sets of varying widths. The maximum size that can be stored is 4G bytes

Questions about Oracle BULK INSERT data