update:(5 hours later after Nicolas Gasparotto’s comment) the post below is a good explanation for invalid dba exception I got for my own actions. You need to read the post together with first two comments.
I have 2 excuses for my dodgy conclusion below:)
1- I am on diet and halved the calorie intake, so my brain is not fed enough.
2- The way it gives the same result on every different/client machine combination lead me wrong idea and to be honest, I wasnt focusing on the content of trigger because of a successful case.
At the end of the day I am %100 guilty because I act like a developer and blamed ORacle at first place:)
Moral of the story for me ; check before publishing, especially your NLS settings
I think I found a bug which is available for the sqlplus clients above 10.2.0.1 .
What I was doing was an insert on a table. Table had a trigger which inserts a non-default value for a column that wasn’t in the insert list.
Insert statement wasn’t working on my 11.1.0.6 client but works fine on TOAD or SQLPLUSW exe.
This is the test case I prepared for this issue; (exactly the same trigger on our production database)
Table is on 10.2.0.3 database (DB version doesnt matter the problem is client)
SQL> drop table test_c purge;
Table dropped.
SQL> create table test_c (test_count number,tr_date date);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := '31-DEC-2049';
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /
Trigger created.
This is the behaviour for the insert on different client versions for sqlplus.exe (Windows) and sqlplus (HP-UX)
On 10.2.0.1 client the tests case works.
SQL> insert into test_c (TEST_COUNT) values (0);
1 row created.
On 10.2.0.2 client test case fails.
windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'
Unix
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'
On 10.2.0.4
windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'
Unix
On 11.1.0.6
Windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'
I don’t have a unix 11G client, but I think it will fail on it as well.
I think its time to raise an SR to Oracle Support.
It is also time for you to use TO_DATE function :
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test_c (test_count number,tr_date date);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := to_date(’31-DEC-2049′,’DD-MON-YYYY’);
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /
Trigger created.
SQL> insert into test_c (TEST_COUNT) values (0);
1 row created.
SQL>
Comment by Nicolas Gasparotto — March 23, 2009 @ 5:55 pm
The following example will give you a clue, since you are not using TO_DATE function as it should, everuthing depend of your nls_date_format session parameter value :
SQL> create table test_c (test_count number,tr_date date);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := ’31-DEC-2049′;
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /
Trigger created.
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at “SCOTT.TRG_TST”, line 2
ORA-04088: error during execution of trigger ‘SCOTT.TRG_TST’
SQL> alter session set nls_date_format=’dd-mon-yyyy’;
Session altered.
SQL> drop table test_c purge;
Table dropped.
SQL>
SQL> create table test_c (test_count number,tr_date date);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := ’31-DEC-2049′;
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /
Trigger created.
SQL> insert into test_c (TEST_COUNT) values (0);
1 row created.
SQL>
Hope that help to understand the TO_DATE function should be mandatory.
Comment by Nicolas Gasparotto — March 23, 2009 @ 6:01 pm
Shame on me:)
I checked it by myself.
I checked it with my team mate to be sure if I was missing something.
We both did not focuse on the content of trigger.
He lead me wrong I lead him wrong. We were both wrong with our conclusions.
I can accept missing to catch absence of to_date but I cant accept ignoring or not checking NLS settings
By the way, If I was the developer, I have never wrote that code without to_date but sometimes I can have the aim to assume that it was written with to_date:))
Thank you for your correction and comment. I have updated the title and the post.
Comment by coskan — March 23, 2009 @ 8:23 pm
Sometimes we need “fresh” eyes on a focused problem
Comment by Nicolas Gasparotto — March 24, 2009 @ 2:50 pm