[Solved] Oracle :value too large for column “SCHEMA”.”TABLE”.”COLUMN” (actual: 519, maximum: 500)

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.

Similar Posts: