Insert Error on Oracle 11g but Not on 10g

2 06 2011

June 2, 2011

The most popular article on this blog includes a script that demonstrates generating a deadlock on Oracle Database 11g R1 and R2 when the exact same script on Oracle Database 10g R2 does not trigger a deadlock.  I came across another interesting change between Oracle Database 10.2.0.5 and 11.2.0.2 (the change also appears in 11.1.0.7) that affects the results of Insert statements that seemingly worked fine for years.

We will use the following simple script for this blog article:

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

First, a pop quiz:

What is the expected output of the select from table T2?

 Answer A:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

10 rows selected.

Answer B:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

11 rows selected.

Answer C:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0

12 rows selected.

Answer D:

no rows selected

Answer E:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10
02-JUN-11         11
02-JUN-11         12

12 rows selected.

Answer F:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          1

2 rows selected.

—————————————————–

Think about your answer for a minute… you might even want to try the experiment on an Oracle Database.  Scroll down when you think that you have the answer.

Script Output from Oracle Database 10.2.0.5:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

SQL> SELECT * FROM T2;

C1                C2
--------- ----------
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0

12 rows selected.

Script Output from Oracle Database 11.2.0.2:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);
INSERT INTO T1 VALUES(SYSDATE-10,-10)
            *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;
  T1
  *
ERROR at line 2:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);
INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM)
                              *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.

SQL> SELECT * FROM T2;

no rows selected

The results for Oracle Database 11.1.0.7 are identical to those of Oracle Database 11.2.0.2.

The answer to the quiz question is therefore… C AND D, which is of course logically impossible.  Disappointed?  Maybe there is a bug ID in Metalink (MOS) for this particular problem.  :-)  If you search the web, you can find similar SQL statements in various Internet forums, for example on the OTN forums.


Actions

Information

9 responses

2 06 2011
Charles

Charles,

Thats a good point and it’s indeed seems to be a bug.

Thanks,
Jay

2 06 2011
Jay

On 10.2.0.5:

14:46:20 SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

Elapsed: 00:00:00.07
16:50:33 SQL> 16:50:33 SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

Elapsed: 00:00:00.04
16:50:37 SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/
16:50:42 2 16:50:42 3 16:50:42 4 16:50:42 5 16:50:42 6 16:50:42 7
Trigger created.

Elapsed: 00:00:00.15
16:50:42 SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

Elapsed: 00:00:00.04
16:50:46 SQL> INSERT INTO
T1
SELECT
SYSDATE+ROWNUM,
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

Elapsed: 00:00:00.00
16:50:54 SQL>
16:51:01 SQL>
16:51:01 SQL>
16:51:01 SQL>
16:51:01 SQL>
16:51:02 SQL>
16:51:02 SQL> select * from t2;

C1 C2
——— ———-
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0
02-JUN-11 0

C1 C2
——— ———-
02-JUN-11 0

12 rows selected.

Elapsed: 00:00:00.01
16:51:07 SQL>

3 06 2011
Charles Hooper

Jay,

Thank you for confirming the results on Oracle Database 10.2.0.5.

2 06 2011
Jimmy

Excellent post, Charles! Not to be picky. But when it comes to triggers I never understood the point of “REFERENCING NEW AS NEW”.

3 06 2011
Charles Hooper

Jimmy, regarding the “REFERENCING NEW AS NEW” entry, I was originally planning to extend the example, but decided against doing so. For that reason, it probably looks like I was working from the “Dummies” book (my PL/SQL abiity is a little rusty, but I have never used that book) where the “REFERENCING NEW AS NEW” clause is found:

http://books.google.com/books?id=NDTPCe8tM94C&pg=PA175&lpg=PA175#v=onepage&q&f=false

I would prefer to say that I was intending to alias “NEW” as something else due to a potential naming conflict:

http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10472/triggers.htm#LNPLS749

As my sample code is written, there is no point in the “REFERENCING NEW AS NEW” phrase in the trigger code (I think that this is what you are implying).

I assume that the person in the forum thread was attempting to do something like the following, just without realizing that a sequence must be created to generate the sequential numbers:

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE SEQUENCE T2_C2;

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (:NEW.C1,T2_C2.NEXTVAL);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

The output of the above follows (note that this is similar to Answer B above):

SQL> SELECT * FROM T2;
 
C1                C2
--------- ----------
24-MAY-11          1
04-JUN-11          2
05-JUN-11          3
06-JUN-11          4
07-JUN-11          5
08-JUN-11          6
09-JUN-11          7
10-JUN-11          8
11-JUN-11          9
12-JUN-11         10
13-JUN-11         11
 
11 rows selected.

As demonstrated below, the “REFERENCING NEW AS NEW” clause is not necessary:

DROP SEQUENCE T2_C2;
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE SEQUENCE T2_C2;

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (:NEW.C1,T2_C2.NEXTVAL);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;
SQL> SELECT * FROM T2;

C1                C2
--------- ----------
24-MAY-11          1
04-JUN-11          2
05-JUN-11          3
06-JUN-11          4
07-JUN-11          5
08-JUN-11          6
09-JUN-11          7
10-JUN-11          8
11-JUN-11          9
12-JUN-11         10
13-JUN-11         11

11 rows selected.

Of course if we have a name collision with the word “NEW”, we can specify the “REFERENCING NEW AS” clause:

DROP SEQUENCE T2_C2;
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE SEQUENCE T2_C2;

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW_NO_NAME_COLLISION
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (:NEW_NO_NAME_COLLISION.C1,T2_C2.NEXTVAL);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;
SQL> SELECT * FROM T2;

C1                C2
--------- ----------
24-MAY-11          1
04-JUN-11          2
05-JUN-11          3
06-JUN-11          4
07-JUN-11          5
08-JUN-11          6
09-JUN-11          7
10-JUN-11          8
11-JUN-11          9
12-JUN-11         10
13-JUN-11         11

11 rows selected.
2 06 2011
Houri Mohamed

Charles,

I have reproduced the same behaviour in 11.2.0.1.0 (don’t be disturbed by the error displayed in french)

mhouri@mhouri> select * from v$version;

BANNER
——————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

mhouri@mhouri> INSERT INTO
2 T1
3 SELECT
4 SYSDATE+ROWNUM,
5 ROWNUM
6 FROM
7 DUAL
8 CONNECT BY
9 LEVEL<=10;
T1
*
ERREUR Ó la ligne 2 :
ORA-00976: Pseudo-colonne ou opÚrateur indiquÚ non autorisÚ ici.
ORA-06512: Ó "MHOURI.T1_AFTER_INSERT", ligne 2
ORA-04088: erreur lors d'exÚcution du dÚclencheur 'MHOURI.T1_AFTER_INSERT'

Best regards

3 06 2011
Charles Hooper

Mohamed,

Thank you for reproducing the error on 11.2.0.1.

Anyone running Oracle Database 9i (or 8i) that can test the script?

7 06 2011
William Robertson

Still off-topic, but name collisions with NEW and OLD can only occur with tables that are named ‘NEW’ or ‘OLD’, and even then it is only ‘to avoid confusion’ and a referencing clause is not needed. If anything, I think the referencing clause has increased the amount of confusion. Code generators often have to include this sort of clause because they can’t tell whether you set it explicitly.

10 06 2011
Charles Hooper

William,

Thank you for the additional information regarding the refererencing clause.

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




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: