March 23, 2009

SQL*Plus Bug or My own bug :)

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)

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
WindowsSQL> 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.

Like this:

Related

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

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);

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’