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

6 06 2011

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:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  ORDER_ID VARCHAR2(20),
  PROCESSED NUMBER);

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

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):

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/ 

The output from Oracle Database 11.2.0.2 looks like this:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.
 

The above worked as expected, the anonymous PL/SQL blocks output TESTING, TESTING2, and TESTING3.  Not particularly useful as written, but it executes and outputs the expected information.

Let’s create a non-unique index on the ORDER_ID column and repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE INDEX IND_T1 ON T1(ORDER_ID); 

The output from Oracle Database 11.2.0.2 showing the anonymous PL/SQL:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

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:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 ADD PRIMARY KEY(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

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:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID));

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion of the output:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1
IND_T1_FB

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

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):

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 DROP PRIMARY KEY;
DROP INDEX IND_T1;
DROP INDEX IND_T1_FB;
CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 (and 10.2.0.5):

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

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:

SQL> CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

Index created.

SQL>
SQL> SELECT
  2    BANNER
  3  FROM
  4    V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

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:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

DROP INDEX IND_T1_UNIQUE;
CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID)); 

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;
/ 

Let’s retry the anonymous PL/SQL blocks:

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

The output of the anonymous PL/SQL blocks:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed. 

Note that the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Let’s try fixing the package so that it will not 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 /*+ NO_INDEX(T1 IND_T1_FB) */
      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;
/ 

Now, reset the table’s rows:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Finally, the output from the anonymous PL/SQL blocks on Oracle Database 11.2.0.2:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed. 

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.








Follow

Get every new post delivered to your Inbox.

Join 142 other followers