This content has been marked as final. Show 8 replies
1 person found this helpful
804947 wrote:the trace file from above should contain the bind values for each column
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
2) DBMS_MONITOR.session_trace_enable/disable and even
3) alter system set events '10046 trace name context forever, level 12' and;
I've just done a couple of tests (on 126.96.36.199) 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.1 person found this helpful
Unless perhaps you have the trace_enabled instance parameter on FALSE?
As I know ORA-01438 is for numeric Vaule , and ORA-12899 for Varchar2 , maybe this will help you1 person found this helpful
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!
would it be helpful to capture the stement using audit? Like this,1 person found this helpful
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>
auditing is valid option also , but you can trace with level 14381 person found this helpful
alter system set events='1438 trace name Errorstack forever,level 10';
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')
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 ...!