Unique Index Result Error with Ref Cursor on Oracle 11g but Not on 10g

6062011

June 6, 2011

You might expect to see some differences when upgrading from one release version of Oracle Database to another release version (edit June 6, 2011:see this article for expected changes when upgrading from 10g to 11g). For instance, you might find that Oracle Database 11.1.0.6 deadlock when Oracle Database 10.2.0.5 and below do not. You might find that Oracle Database 11.1.0.6 and above throw error messages when ROWNUM is used when Oracle Database 10.2.0.5 and below do not throw error messages. You might find that the following SQL statement (based on the contents of this article) returns different values for the SYS user depending on the Oracle Database release version:

SELECT SYS_CONTEXT('USERENV', 'SESSIONID') USERENV FROM DUAL;

For example, on Oracle Database 11.2.0.2 when SYS connects as SYSDBA, you will see something like this for the output of that SQL statement:

USERENV
----------
4294967295

If the O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE on 11.2.0.2, and the SYS user connects without specifying AS SYSDBA, you might see something like this for the output of that SQL statement:

USERENV
-------
344841

If the SYS user connects on Oracle Database 9i, you will probably see something like this for the output of that SQL statement:

USERENV
-------
0

The above are all minor changes in bahavior. If you are brave, go ahead and blindly upgrade to a new version of Oracle Database. :-)

While browsing a couple of web forums I found another example of changed results caused by upgrading from Oracle Database 10g (possibly 10.2.0.4 or lower) to 11g (not stated, but possibly 11.2.0.2). To be kind, my PL/SQL coding abilities are rusty, but I think that I follow the logic in that forum thread. Let’s see if we are able to reproduce the problem with a couple of slight modifications. First, let’s create a table with three rows:

Now create a PL/SQL package with an embedded COMMIT (as stated in the forum thread linked to above, this embedded COMMIT is included because this example is from a stateless web application):

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
TYPE T1_CURSOR IS REF CURSOR;
PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
BEGIN
OPEN T1_CURSOR_CURSOR FOR
SELECT
ORDER_ID
FROM
T1
WHERE
ORDER_ID= ORDER_ID_VARCHAR
AND PROCESSED=0;
UPDATE
T1
SET
PROCESSED=1
WHERE
ORDER_ID= ORDER_ID_VARCHAR;
COMMIT;
END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/

Hopefully, I haven’t lost anyone yet, in the above: select a row, return the row as a ref cursor, and then update the row to show that the row has been processed. Now let’s process the three orders using three anonymous PL/SQL blocks (it is not necessary to use three anonymous PL/SQL blocks, but this was done to verify that the problem happens on multiple executions also):

The above test seemed to work as expected… TESTING, TESTING2, TESTING3. Let’s create a primary key constraint on the ORDER_ID column, which will use the existing IND_T1 index to enforce the primary key constraint, then we will repeat the anonymous PL/SQL portion of the test:

Nothing unusual yet – you are probably wondering about the point of this article by now, almost there. Let’s create a unique function based index on UPPER(ORDER_ID) and then repeat the anonymous PL/SQL portion of the test:

Still nothing unusual yet. Let’s try one more time, removing the primary key constraint, dropping the two indexes, and creating a single unique index on the ORDER_ID column (in case you are wondering, with just the function-based unique index on the table there were no problems):

Note that this time the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3. Oracle Database 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 all exhibit this unexpected behavior when a unique index is present on table T1’s ORDER_ID column.

Oracle Database 10.2.0.4 does not exhibit this problem. Below is the output from Oracle Database 10.2.0.4 for the last portion of the script:

Well, that is a little interesting. Let’s try another test – remember that we had no trouble with the unique function-based index. Let’s quickly revisit that example. First, reset the T1 test table, drop the unique index, and then recreate the unique function-based index:

Now let’s modify the package so that it will likely use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
TYPE T1_CURSOR IS REF CURSOR;
PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
BEGIN
OPEN T1_CURSOR_CURSOR FOR
SELECT
ORDER_ID
FROM
T1
WHERE
UPPER(ORDER_ID)= ORDER_ID_VARCHAR
AND PROCESSED=0;
UPDATE
T1
SET
PROCESSED=1
WHERE
UPPER(ORDER_ID)= ORDER_ID_VARCHAR;
COMMIT;
END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/

Testing 1, 2, 3. I think that I just fixed a problem by using a hint… although we could have avoided the problem by creating a non-unique normal index with a primary key constraint, or by creating a non-unique index with a second unique function-based index to enforce the uniqueness in the column.

—

Added June 6, 2011:

The linked forum thread suggests that the problem might be identified in Metalink (MOS) Bug 10425196: “PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5″. I was able to reproduce the problem in Oracle Database 10.2.0.5 on the Windows platform. Also, note in the above tests that it is not necessarily the presence of a primary key index on the table that could cause the problem – as shown above, the problem is apparently caused by cases where a unique index is used to retrieve the rows for the ref cursor.

I believe that that is the version when a commit inside a loop driven by a select for update started to raise an ora-1002 fetch out of sequence (a good change to protect against a flawed coding habit).

Thanks for providing the link to Jonathan’s article. I remembered reading a quote somewhere, but I could not remember where:

“It doesn’t really matter whether you think the old behaviour or the new behaviour is correct – the problem is that the behaviour has changed in a way that could silently produce unexpected results.”

Are you able to confirm that you see the problem in 10.2.0.5. I tested 10.2.0.5 in 64 bit Windows and was able to reproduce the problem. I then tested 10.2.0.4 in 32 bit Windows and could not reproduce the problem. The MOS (Metalink) Bug article states that the problem is not present in 10.2.0.5 on one of the Unix platforms (Solaris), but I am curious to know if the problem is found only in the Windows release of 10.2.0.5 (it was released after most of the Linux/Unix platforms and included additional bug fixes).

I’m just doing an upgrade from 9.2.0.8 to 11.2.0.2 and a whole rack of PLSQL code doing exactly this was just revealed, thanks to this behaviour change (thank goodness). It may be that as far back as 7 that the behaviour was the same, I can’t remember now. Certainly in 9.2.0.8 the commit just releases the locks.

I can’t now seem to find the metalink note that I had previously regarding this change being explicitly implemented in 10.2.0.5.

I had wondered if the reason why this is working with a non-unique index and a full table scan, but not a unique index is the result of Oracle Database restarting the transaction, as described here in Tom Kyte’s “Expert Oracle Database Architecture” book:http://books.google.com/books?id=HPJDlGmecwcC&pg=PA263
A before update trigger on table T1indicated that the updated only happened once, and it happened before the PL/SQL route output “Processing is complete” (that was expected).

For fun I enabled a 10046 extended SQL trace and a 10200 consistent gets trace. Unless I am reading the consistent gets trace incorrectly (that is definitely possible), it does not appear that Oracle Database builds a consistent read copy of the table block when the unique index is used to select the rows, while it does build a consistent read copy of the table block when a non-unique index is used to select the rows:

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: