Oracle Error 1401
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES java.sql.sqlexception inserted value too large for column AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY value too large for column oracle ora-12899 HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE ora-01401 in select statement SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions ora-01041 Analytic Functions Advanced Functions Oracle / PLSQL: ORA-01401 Error Message Learn the cause and how to resolve the ORA-01401 error message in Oracle. Description When you encounter an ORA-01401 error, the following error message will appear: ORA-01401: inserted value too large for column Cause You tried to insert a value into a column that exceeds the maximum width for the column. Resolution The option(s)
Ora12899
to resolve this Oracle error are: Option #1 Correct your SQL to truncate the value so that it fits within the field. You can always try the SUBSTR function to truncate the value. Option #2 Modify your table definition to allow for a larger value in a field. This can be done with a ALTER TABLE command. For example, if you had a table called suppliers defined as follows: CREATE TABLE suppliers ( supplier_id number not null, supplier_name varchar2(10) ); And you tried to execute the following INSERT statement: INSERT INTO suppliers ( supplier_id, supplier_name ) VALUES ( 10023, 'Hewlett Packard' ); You would receive the following error message: You have defined the supplier_name column as a varchar2 that can only handle up to 10 characters. Yet, you have attempted to insert the value 'Hewlett Packard' (which is 15 characters in length) into this field. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with
01401 Cz
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow 01401 zip code Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up 01401 area code SQL Error: ORA-01401: inserted value too large for column up vote 2 down vote favorite 1 I am getting issue while inserting a new record to the table in Oracle database. The error description says- SQL Error: ORA-01401: inserted value too large https://www.techonthenet.com/oracle/errors/ora01401.php for column How would I come to know that which column is having large value as I am having 60 columns in the table. oracle oracle10g share|improve this question edited Oct 27 '15 at 13:19 hkutluay 4,33511836 asked Mar 24 '15 at 9:22 Prateek Shukla 338316 Can you please tell us version of your database? You tagged it as 10g, however, it seems to be 9i because ora 01401 was replaced in 10g by ora 12899, which is mentioned in @Lalit s answer. http://stackoverflow.com/questions/29228898/sql-error-ora-01401-inserted-value-too-large-for-column In 10g the column name is included in err msg. –Mina Mar 24 '15 at 10:01 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted SQL Error: ORA-01401: inserted value too large for column You are trying to insert value larger than the specified size for the column. How would I come to know that which column is having large value as I am having 60 columns in the table. The error will certainly have the table and column name with the actual size being inserted and the maximum size allowed. For example, SQL> CREATE TABLE t(A VARCHAR2(2)); Table created. SQL> SQL> INSERT INTO t VALUES ('123'); INSERT INTO t VALUES ('123') * ERROR at line 1: ORA-12899: value too large for column "LALIT"."T"."A" (actual: 3, maximum: 2) SQL> In the above example, the error clearly states "column "LALIT"."T"."A" (actual: 3, maximum: 2)" where LALIT is the SCHEMA, T is the TABLE and A is the COLUMN. The size specified for column A while table creation was 2, however, the actual insert had 3. UPDATE Regarding confusion between ORA-01401 and ORA-12899. From Oracle 10g and higher, the ORA-01401 was modified to ORA-12899 which is more explicit and has the details about the SCHEMA, TABLE and the COLUMN which caused the error. Additional information Just in case if anyone is interested: There is a counterpart of ORA-01401, i.e. ORA-01438 which is applicable in case of NUMBER. This seems to be unchanged. For example, SQL> CREATE TABLE
errors and status changes using the SQLSTATE, SQLCA, SQLCODE, and WHENEVER statements. You also learn how to diagnose problems using the ORACA. SQLSTATE, the SQLCA, and https://docs.oracle.com/cd/E15817_01/appdev.111/b31230/ch2.htm SQLCODE Release 1.6 provides forward and backward compatibility with regard to checking http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=29263&DestinationA=RSS the outcome of executing SQL statements. The SQLCA data structure containing status information and SQLCODE status variable can be used in the same manner as in previous releases. The SQLSTATE status variable is introduced in release 1.6. Declaring SQLSTATE When MODE=ANSI, you must declare SQLSTATE or SQLCODE. Declaring the value too SQLCA is optional. When MODE=ORACLE, not declaring the SQLCA causes compile time warnings and runtime errors. Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character strings and must be declared inside the Declare Section. You declare SQLSTATE as: DCL SQLSTATE CHAR(5); Note: SQLSTATE must be declared with exactly 5 characters. SQLSTATE Values SQLSTATE status value too large codes consist of a 2-character class code followed by a 3-character subclass code. Except class code 00 ("successful completion"), the class code denotes a category of exceptions. And, except subclass code 000 ("not applicable"), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value '22012' consists of class code 22 ("data exception") and subclass code 012 ("division by zero"). Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in SQL92). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined subconditions. All other subclass codes are reserved for implementation-defined subconditions. shows the coding scheme. Figure 2-1 SQLSTATE Coding Scheme Description of "Figure 2-1 SQLSTATE Coding Scheme" Table 2-1 shows the classes predefined by SQL92. Table 2-1 Predefined Classes Cla