Reason: I use create table XXX as subquery to create a data table, which is mainly used to aggregate the relevant data, and then export it as an SQL script file to import it into the new library, resulting in inconsistent field types and lengths of some insert into statements due to inconsistent database character sets, This will fail to insert value too large for column “schema” “TABLE”. “COLUMN” (actual: 519, maximum: 500) ； Because the length is incompatible
The solution is to use convert to convert the character set or cast to increase the length of the type on the error field of the corresponding select, and then create table will be the type of table structure according to the field type you set; However, after using convert to convert the character set, the query will result in ora-29275: partial multibyte character error.
So my solution here is to use cast conversion type
CREATE TABLE XXX AS
SELECT CAST(col AS VARCHAR(1000)),… FROM xxx;
Cast function document: https://docs.oracle.com/database/121/SQLRF/functions024.htm#SQLRF00613
Example of using the convert function:
CREATE TABLE XXX AS
SELECT CONVERT(col,’AL32UTF8′),… FROM xxx;
Convert function document: https://docs.oracle.com/database/121/SQLRF/functions041.htm#SQLRF00620
If only the CONVERT SELECT clause is executed, an error will occur ORA-29275: partial multibyte character, but CREATE TABLE XXX AS SELECT will be executed together without error, but the table generated by the query will also report an error ORA-29275.
- [Solved] The length of the data truncation done by springboothinesdata for the column.
- used in key specification without a key length
- [Solved] SQL Error (3621): String or binary data would be truncated The statement has been terminated.
- [Solved] MySQL Add New Field Error: ERROR 1118 — Row size too large. The maximum row size for the used table type
- Solution of data truncated for column ‘xxx’ in MySQL
- C# Unable to translate set operation when matching columns on both sides have different store types
- SQL Error: 1064, SQLState: 42000 [Three Methods to Solve]
- Error: Unknown column ‘*_image_url’ in ‘field list’ [How to Solve]
- Error in modifying data element data type [How to Solve]
- [Solved] Oracle Import Error:field in data file exceeds maximum length