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

Date : June 2, 2011
Categories : General Administration, Quiz - Whose Answer is it Anyway?

9 responses

2 06 2011

Charles (03:53:26) :

Charles,

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

Thanks,
Jay

2 06 2011

Jay (03:54:19) :

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 (07:43:46) :

Jay,

Thank you for confirming the results on Oracle Database 10.2.0.5.

2 06 2011

Jimmy (08:34:34) :

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 (07:42:10) :

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 (09:57:47) :

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 (07:47:55) :

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 (06:38:14) :

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 (07:42:49) :

William,

Thank you for the additional information regarding the refererencing clause.

	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Huge Presentation – Work…
	Dan on Huge Presentation – Work…
	Charles Hooper on Review of Synology DS415+, How…
	Matthias Clausen on Review of Synology DS415+, How…
	Charles Hooper on _SMALL_TABLE_THRESHOLD Paramet…
	mohd atif on _SMALL_TABLE_THRESHOLD Paramet…
	Charles Hooper on Free ANSI SQL to Oracle Specif…
	Kalin Agrawal on Free ANSI SQL to Oracle Specif…
	Charles Hooper on Large Page Support or Not…
	vaurob on Large Page Support or Not…
	Locating Oracle… on Finding a New Home for a Clien…

Charles Hooper's Oracle Notes