Error 01438 Oracle
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 ora 01438 error in oracle more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags ora-01438 how to find column name Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like sql error: 1438, sqlstate: 22003 you, helping each other. Join them; it only takes a minute: Sign up ORA-01438: value larger than specified precision allows for this column up vote 7 down vote favorite We get sometimes the following error from our partner's
How To Resolve Ora-01438
database: ORA-01438: value larger than specified precision allows for this column The full response looks like the following:
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp ora-01438 number Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB
Oracle Error 1438
Don Burleson Blog
ORA-01438:Sql State 22003
value larger than specified precision allowed for this column tips Oracle Error Tips by Burleson Consulting (S. Karam) The Oracle docs note this http://stackoverflow.com/questions/181344/ora-01438-value-larger-than-specified-precision-allows-for-this-column on the ora-01438 error: ORA-01438: value larger than specified precision allowed for this column Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column. Action: Enter a value that complies with the numeric column"s precision, or use the MODIFY option with http://www.dba-oracle.com/t_ora_01438_value_larger_than_specified_precision_allowed_for_this_column.htm the ALTER TABLE command to expand the precision. Because ORA-01438 often consists of an erred entry in your SQL, it is often the case that syntax needs to be evaluated. For example, in the Oracle Technology Network's forums, a user consistently received ORA-01437 when he is running his procedure. His replier pointed out that the values entered may exceed the column definition, or he was entering the CHAR value in the NUMBER field. From that information, ORA-01438 was successfully resolved. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish thei
Samples & SAS Notes Focus Areas SUPPORT License Assistance Manage My Software Account Downloads & Hot Fixes TRAINING & BOOKS Books Training Certification SAS Global Academic Program SAS OnDemand For Academics USERS GROUPS Advanced Search support.sas.com http://support.sas.com/kb/19/214.html Knowledge Base Support Training & Books Store Support Communities Knowledge Base Products & Solutions System Requirements Install Center Third-Party Software Reference Documentation Papers Samples & SAS Notes Browse by Topic Search Samples http://grokbase.com/t/freelists.org/oracle-l/072nnfzje8/how-do-i-get-column-name-that-causing-ora-01438 Search Usage Notes Search Installation Notes Search Problem Notes Focus Areas Usage Note 19214: ora-01438: value larger than specified precision allows for this column The following error may be issued whern accessing Oracle in oracle data using SAS/ACCESS to load an Oracle table using insert table as select * from sastable. . ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allows for this column. The issue appears to be related to the fact that if a table in Oracle contains a field defined as number(2,5), Oracle will allow the table to be created without error. However, Oracle will not allow error 01438 oracle any entry into this table if the improperly defined attribute is not null (and will only give an error when the attribute is attempted to be loaded). This is why the columns creating the error could not be found. To identify the error, put the dba entry test data into each column one at a time. Once you get the columns where the precision was greater than the field width, you are able to isolate the issue to just those columns. To resolve this problem, redefine the table. In this example, the proper definition for this field in Oracle is number(7,5). In Oracle the 7 = width of the field and the 5 is the number of digits to the right of the decimal point. Once the table is redefined in Oracle and in SAS, you will be able to load the table using insert table as select * from sastable. Operating System and Release InformationProduct FamilyProductSystemSAS ReleaseReportedFixed*SAS SystemSAS/ACCESS Interface to Oracle64-bit Enabled HP-UX9.1 TS1M364-bit Enabled Solaris9.1 TS1M3Linux9.1 TS1M364-bit Enabled AIX9.1 TS1M3* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed. Type:Usage NotePriority:
which I can found out the column name causing anORA-01438?I tried tracing the session, unfortunately, the trace file doesn't have anycluses about the column causing this error.Any link or workaround is appreciated.--Best Regards,Syed Jaffar HussainOracle ACE8i,9i & 10g OCP DBAhttp://jaffardba.blogspot.com/http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain----------------------------------------------------------------------------------"Winners don't do different things. They do things differently."--http://www.freelists.org/webpage/oracle-l reply Tweet Search Discussions Search All Groups oracle-l 14 responses Oldest Nested Mladen Gogala ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12'; -- Mladen Gogala Sr. Oracle DBA Video Monitoring Systems 1500 Broadway New York City, NY 10036 Phone: (212) 329-5201 Email: mgogala_at_vmsinfo.com -- http://www.freelists.org/webpage/oracle-l Mladen Gogala at Feb 21, 2007 at 4:34 pm ⇧ Syed Jaffar Hussain wrote:Hi List,Is there a way though which I can found out the column name causing anORA-01438? I tried tracing the session, unfortunately, the trace file doesn'thave any cluses about the column causing this error.Any link or workaround is appreciated.ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';--Mladen GogalaSr. Oracle DBAVideo Monitoring Systems1500 BroadwayNew York City, NY 10036Phone: (212) 329-5201Email: mgogala_at_vmsinfo.com--http://www.freelists.org/webpage/oracle-l reply | permalink Syed Jaffar Hussain Mladen, Yes, I do set the event to trace the culprit. The problem is that when I enable this event, Oracle is taking around 6 second to return the error msg. on the sql prompt. And ours is a very high OLTP application where around 500 tps take places. We have request Oracle for an enhancement. Because, when constraints violates, Oracle do gives the constrain name and details, likewise, I would like to have so and so column in the particular table is the culprit. Regards Jaffar -- Best Regards, Syed Jaffar Hussain at Feb 21, 2007 at 8:32 pm ⇧ Mladen,Yes, I do set the event to trace the culprit. The problem is that when Ienable this event, Oracle is taking around 6 second to return the error msg.on the sql prompt. And ours is a very hig