8 Replies Latest reply: Dec 3, 2012 8:16 AM by 807950 RSS

    ORA-01438: How to find column name?

    807950
      Hello,

      I'm trying to find the column that's causing ORA-01438 when inserting into a table.

      The insert is done by a C-program that reads from a network socket and writes to an oracle 10g database using Oracle Pro*C.

      I have tried to identify the column by printing out the (approximately 250) values just before the insert statement is executed, but checking these values was a cumbersome and error prone task and did not help fix the error (I found a bunch of values that where out of range, but changing these values did not solve the issue).

      Abandoning this idea, I tried to trace the session using
      1) DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION,
      2) DBMS_MONITOR.session_trace_enable/disable and even
      3) alter system set events '10046 trace name context forever, level 12' and;
      4) alter system set sql_trace=true scope=memory;

      Only DBMS_MONITOR.session_trace_enable gave me some hints (the insert statement) in a trace file. But this only worked for a simple example on a test database.

      When I try to enable tracing on the database, where the error occurs, then I'm not even able to set SQL_TRACE in V$SESSION to TRUE even though no error is thrown by the "DBMS_MONITOR.session_trace_enable" statement. The value of SQL_TRACE in V$SESSION just does not change and I don't have any clue why. On the test database it works fine and I can find the trace file in the user_dump_destination directory.

      Does anyone have any suggestions on how to solve this problem?

      Any help is very much appreciated.
      Thanks in advance!
        • 1. Re: ORA-01438: How to find column name?
          sb92075
          804947 wrote:
          Hello,

          I'm trying to find the column that's causing ORA-01438 when inserting into a table.

          The insert is done by a C-program that reads from a network socket and writes to an oracle 10g database using Oracle Pro*C.

          I have tried to identify the column by printing out the (approximately 250) values just before the insert statement is executed, but checking these values was a cumbersome and error prone task and did not help fix the error (I found a bunch of values that where out of range, but changing these values did not solve the issue).

          Abandoning this idea, I tried to trace the session using
          1) DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION,
          2) DBMS_MONITOR.session_trace_enable/disable and even
          3) alter system set events '10046 trace name context forever, level 12' and;
          the trace file from above should contain the bind values for each column
          • 2. Re: ORA-01438: How to find column name?
            JohnWatson
            I've just done a couple of tests (on 11.2.0.3) and you are right: v$session.sql_trace remains DISABLED after ALTER SESSION SET SQL_TRACE=TRUE and ALTER SYSTEM SET SQL_TRACE=TRUE but trace files are generated none the less. Take a look, you'll see them.
            Unless perhaps you have the trace_enabled instance parameter on FALSE?
            • 3. Re: ORA-01438: How to find column name?
              Osama_Mustafa
              As I know ORA-01438 is for numeric Vaule , and ORA-12899 for Varchar2 , maybe this will help you
              • 4. Re: ORA-01438: How to find column name?
                807950
                Yes,t is true that the ALTER SYSTEM command generated trace files, but they don't seem to contain any hint!?

                While the DBMS_MONITOR.session_trace_enable command generated trace files where I could see the error (err=1438) and the sql statement (insert into test_tab(test_col) values (test_val)) with the exact column values, the traces generated by ALTER SYSTEM don't even show the error message! I have also used tkprof to make the trace files more readable, but to no avail.

                The main problem is, that DBMS_MONITOR.session_trace_enable does only work on the test database. On the test database the DBMS_MONITOR... command also changes the sql_trace column value in v$session to true but this does not work on the other database. The value stays false!

                The trace_enabled instance parameter is set to true in both databases. The test database (where the tracing works!) is an oracle 10.2.0.1.0 version while the other database is on version 10.2.0.4.0.

                Today I tried to use an on insert trigger to catch the wrong values but it seems (and it's perfectly logical) that ORA-01438 occurs when the :new-variables are set and that is before I can output any of the values.

                I also tried to write down the output of the C-program and manually create an insert statement. I could find another value that was too large and after changing this value, the manual insert worked fine.

                Anyhow, when I tried to limit the values in the C-code, it didn't work!!! I still get ORA-01438 on insert.

                Is there any way to get DBMS_MONITOR.SESSION_TRACE_ENABLE running on the 10.2.0.4.0 database? It would really help to see the actual values that are being passed to the database!

                So far thank you all for your help!
                • 5. Re: ORA-01438: How to find column name?
                  JohnWatson
                  would it be helpful to capture the stement using audit? Like this,
                  orcl> desc dept
                   Name                                Null?    Type
                   ----------------------------------- -------- ------------------------
                   DEPTNO                              NOT NULL NUMBER(2)
                   DNAME                                        VARCHAR2(14)
                   LOC                                          VARCHAR2(13)
                  
                  orcl> audit insert on scott.dept whenever not successful;
                  
                  Audit succeeded.
                  
                  orcl> insert into scott.dept values(999,null,null);
                  insert into scott.dept values(999,null,null)
                                                *
                  ERROR at line 1:
                  ORA-01438: value larger than specified precision allowed for this
                  column
                  
                  
                  orcl> select sql_text,returncode from dba_audit_trail
                    2  where owner='SCOTT' and obj_name='DEPT';
                  
                  SQL_TEXT
                  ----------------------------------------------------------------------
                  RETURNCODE
                  ----------
                  insert into scott.dept values(999,null,null)
                        1438
                  
                  
                  orcl>
                  • 6. Re: ORA-01438: How to find column name?
                    Osama_Mustafa
                    auditing is valid option also , but you can trace with level 1438
                    alter system set events='1438 trace name Errorstack forever,level 10';
                    • 7. Re: ORA-01438: How to find column name?
                      Osama_Mustafa
                      Just check this :
                      SQL > conn osama/osama ;
                      SQL> create table test as select * from scott.dept ;
                      
                      SQL> select * from test ;
                      
                          DEPTNO DNAME          LOC
                      ---------- -------------- -------------
                              10 ACCOUNTING     NEW YORK
                              20 RESEARCH       DALLAS
                              30 SALES          CHICAGO
                              40 OPERATIONS     BOSTON
                      
                      
                      SQL> alter system set events='1438 trace name Errorstack forever,level 10';
                      
                      
                      SQL> insert into test values (100000000000000000,'osama','JOR');                         
                      insert into test values (100000000000000000,'osama','JOR')
                                               *
                      ERROR at line 1:
                      ORA-01438: value larger than specified precision allowed for this column
                      
                      In Trace File :
                      
                      ORA-01438: value larger than specified precision allowed for this column
                      Current SQL statement for this session:
                      insert into test values (100000000000000000,'osama','JOR')
                      • 8. Re: ORA-01438: How to find column name?
                        807950
                        I have tried auditing and setting events but had some problems. In the end I have modified the c-program so that it inserts into a dummy table instead.
                        This table resembles the original table but without the restrictions on the number fields (e.g. number instead of number(5,3)).

                        Using this approach, I could find the problematic field relatively easily and limit its value in the c-code (I had tried this before, but made a mistake). Now it works just fine!

                        Nevertheless I wanted to thank all of you (Osama_mustafa, JohnWatson, sb92075) for your efforts and helpful suggestions!!! I'll surely try some of the examples to gain a bit of experience on that topic, as this may come in handy someday ...!