Coskan’s Approach to Oracle

March 23, 2009

SQL*Plus Bug or My own bug :)

Filed under: Bugs — coskan @ 5:00 pm

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.

About these ads

4 Comments »

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

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

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

  4. Sometimes we need “fresh” eyes on a focused problem :-)

    Comment by Nicolas Gasparotto — March 24, 2009 @ 2:50 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: