Oracle Error Code 1401
Contents |
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
Ora-01401 Inserted Value Too Large For Column Oracle
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges java.sql.sqlexception inserted value too large for column Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other.
Value Too Large For Column Oracle Ora-12899
Join them; it only takes a minute: Sign up ORACLE -1401 error up vote 0 down vote favorite I have a stored procedure in Oracle 9i which inserts records in a table. The table has a primary key ora-01401 in select statement built to ensure duplicte rows doesnot exists. I am trying to insert a record by calling this stored procedure and it works first time properly. I am again trying to insert a duplicate record and expecting unique constraint violation error. But I am getting ORA-01401 inserted value too large for column I knew its meaning but my query is , if the value inserted is really large then how it got successful in the first attempt. Table is ora 1401 CREATE TABLE KEY ( ID VARCHAR2(25 BYTE), KEY NUMBER(4) NOT NULL, INSERT_DATE DATE, WORK_KEY VARCHAR2(128 BYTE) ) CREATE UNIQUE INDEX SACHINIDX ON KEY (ID, KEY) Call is EXEC SQL EXECUTE BEGIN keyadd(:id, :key, :wkey); END; END-EXEC; Stored Procedure is PROCEDURE keyadd(id IN VARCHAR2, key IN NUMBER, wkey IN VARCHAR2) { BEGIN INSERT INTO KEY ( ID, KEY, INSERT_DATE, WORK_KEY) VALUES ( id, key, SYSDATE, wkey ); EXCEPTION ROLLBACK; COMMIT; RETURN; END; } First insert sqlca.sqlcode is [0] Second insert sqlca.sqlcode is [-1401] oracle stored-procedures plsql share|improve this question edited Jan 6 '11 at 16:15 asked Jan 6 '11 at 15:47 Sachin Chourasiya 4,877196487 Are there any triggers on the table that could be messing with the values? –kurosch Jan 6 '11 at 15:50 Can you show us a small test case that reproduces this behaviour? –Rob van Wijk Jan 6 '11 at 15:58 Please also show the stored procedure and the call. –Rob van Wijk Jan 6 '11 at 16:07 Added please check –Sachin Chourasiya Jan 6 '11 at 16:16 What are the values for wkey in the first and the second call? –a_horse_with_no_name Jan 6 '11 at 16:25 | show 4 more comments 3 Answers 3 active oldest votes up vote 1 down vote accepted CREATE TABLE KEY ( ID VARCHAR2(25 BYTE), KEY NUMBER(4) NOT NULL, INSERT_DATE DATE, WORK_KEY VARCHAR2(128 B
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
Ora-01041
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
Ora12899
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 http://stackoverflow.com/questions/4616719/oracle-1401-error 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 T
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You https://community.oracle.com/thread/379443 can not post a blank message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 10 Replies Latest reply on Mar 14, 2009 7:47 PM by Peter Gjelstrup ORA-01401: inserted value too large for column 127021 Apr 6, 2006 1:49 PM Hi, Is there any way to find out which value too column is causing inserted value too large for column the problem? I'm trying to insert the data into a table by using cursor into a table in a procedure. My table is having 118 columns. appreciated ur help. Thanks, Ramana. 18317Views Tags: none (add) This content has been marked as final. Show 10 replies 1. Re: ORA-01401: inserted value too large for column Tony value too large Andrews Apr 6, 2006 2:18 PM (in response to 127021) The best way would be to upgrade to 10G where you get improved error messages like this: ORA-12899: value too large for column "SCOTT"."EMP"."ENAME" (actual: 12, maximum: 10) Failing that I think all you can do is test all the data in PL/SQL before trying to insert it. Like Show 0 Likes(0) Actions 2. Re: ORA-01401: inserted value too large for column 127021 Apr 6, 2006 3:17 PM (in response to Tony Andrews) Hi Tony, Thanks a lot for your immediate reply. we are using Oracle 9i. I'm able to find which column it is. But some times we dont know data behaviour some time if may get some asci characters or some how if we can get big string its a problem, i want to handle this in exception part. Thats my concern. Once again thanks a lot. Ramana. Like Show 0 Likes(0) Actions 3. Re: ORA-01401: inserted value too large for column 155651 Apr 7, 2006 8:18 AM (in response to 127021) Create user defined exception using PRAGMA EXCEPTION_INIT DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, -1401); BEGIN insert i