Coskan’s Approach to Oracle

August 13, 2009

DBMS_XMLGEN and bug

Filed under: Basics, Bugs, PL/SQL — coskan @ 4:35 pm

Last 2 weeks I had to deal with a post-upgrade problem (9.2.0.5 to 10.2.0.4) on a system which is not well tested before upgrade.  The issue was error stack below

ORA-06502: PL/SQL: numeric or value error:invalid LOB locator specified: ORA-22275

or for some records

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

Developer was insisting that this is a character set issue because of the first line of the error stack and I was saying it is impossible because both servers were UTF8 and only difference is NLS_NCHAR_CHARACTERSET which is never used anyway.  Because developer already gave the last decision, it was my turn to prove that this wasnt a characterset issue.

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs:) ) .  Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB).   We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem.  DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i.  To be honest The error stack (was using  DBMS_UTILITY.FORMAT_ERROR_STACK and  DBMS_UTILITY.format_error_backtrace)   was not very clear  to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2  as it is working on 9i. The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

This is the demo to show the different behaviour (taken from metalink ).

in 10G


SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

in 9i

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-24347: Warning of a NULL column in an aggregate function

no rows selected

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
--------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

Lessons Learned.

1-Do not trust “we tested its ok”  and Try to understand the application and try to ask developers what packages they are using so you can search for possible bugs before you upgrade the system.

2-After 1 day of struggling, Search metalink first  for a possible bug.  Stop messing around google

March 27, 2009

Good reason to move from 10.2.0.2

Filed under: Bugs — coskan @ 11:31 am

If somebody ask me, which type of bug I prefer to live with from two options which are
1-Performance related bugs 2- wrong resultset bugs. I would prefer to live with performance related ones because wrong resultset is something I never want to see on any of my databases. It is hard to catch and can cause big problems especially on database with Finance data on it.

Today I am going to give you a good reason to move from 10.2.0.2 because of a wrong resultset issue. Issue we hit was on our Peoplesoft database running on HP-UX 11i on Oracle EE 10.2.0.2 . It looks like it is smilar to the
Bug 5843316 Wrong results with index join and function-based index.

Here is my test case for the issue.

My table

create table t1 as
select
mod(object_id, 11000) id,
trunc(dbms_random.value(0,9)) seq,
trunc(dbms_random.value(0,3)) rec,
trunc(created) created,
trunc(last_ddl_time) lastddl,
substr(owner,1,1) status,
object_type,
object_name,
generated,
subobject_name from all_objects;=

Two index on the table (both index have the same four column but second one has two more columns and the 4th column on the index is changed for the second one)


CREATE INDEX T1_IX2 ON T1
(id,rec, created DESC, seq DESC, object_type);


CREATE INDEX T1_IX1 ON T1
(id,rec, created DESC, seq DESC, STATUS, object_name,subobject_name);

Gather the stats

exec dbms_stats.gather_table_stats (ownname=>user,tabname=>'T1',cascade=>TRUE)

On our production case there wasn’t any hint but for simulating the test I am forcing to use indexes to see the different resultset behaviour

These are the SQLs I use for the tests.

wrong result set T1_IX1 hinted


SELECT /*+gather_plan_statistics INDEX (t1 t1_ix1)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

right result set T1_IX2 hinted

SELECT /*+gather_plan_statistics INDEX (t1 t1_ix2)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

These are the runtime behaviours of SQLs
FIRST CASE- Not working when ,resultset column LAST_DDL, is not included in the index T1_IX1. No rows returned

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2 <strong> lastddl</strong>
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected
SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

SECOND CASE- working when resultset column ID which is IN the index T1_IX1.

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 T1_IX1)*/
  2  id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

       ID
----------
        19
        19
        19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Plan hash value: 2740842560

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |   100 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN          |         |     1 |   100 |     7  (15)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN  | T1_IX1  |     1 |    63 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE   |         |     1 |    48 |            |          |
|*  4 |     INDEX RANGE SCAN| T1_IX1  |     1 |    48 |     3   (0)| 00:00:01 |
|   5 |   VIEW              | VW_SQ_1 |    11 |   407 |     3   (0)| 00:00:01 |
|   6 |    HASH GROUP BY    |         |    11 |   814 |     3   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN| T1_IX1  |    11 |   814 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))="VW_COL_1"
              AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("VW_COL_1") AND
              "T1"."ID"="ID" AND "T1"."REC"="REC")
   2 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B1) AND "X"."REC"=:B2 AND
              "X"."ID"=:B3 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B4) AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND( (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B5) AND "X"."REC"=:B6 AND
              "X"."ID"=:B7 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B8)))
   4 - access("X"."ID"=:B1 AND "X"."REC"=:B2 AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B3))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B1)
   7 - access("X"."ID"=19)
       filter("X"."ID"=19)

THIRD CASE- Still not working when one of the resultset columns, LAST_DDL is NOT in the index T1_IX1. No rows returned

 
SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

FOURTH- It is working when we force the database use T1_IX2 for both cases (resultset IN or not IN)

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix2) */
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT  MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

LASTDDL                   ID
----------------- ----------
20070125 00:00:00         19
20070128 00:00:00         19
20090327 00:00:00         19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 3946360691

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX2 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   4 - filter("T1"."STATUS"<>'P')
   5 - access("ID"=19)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

Same resultset on HP-UX-Linux 10.2.0.2. Don’t have 10.2.0.2 windows but works fine on 10.2.0.3 on Windows.

on 10.2.0.4 and 11G problem is gone

This case is enough reason that lead me to check every solved bug for the upper releases to see if our databases are facing the problem especially for wrong resultset. Another good part of looking for bugs, is that it is nice way to learn how to crash your database, a bit of oracle internals, interesting test cases etc.

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.

« Newer Posts

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

Follow

Get every new post delivered to your Inbox.

Join 193 other followers