Ref Cursor with Execute Immediate fails with ORA-604 / ORA-1001 or ORA-600 (Doc ID 1325439.1)

Last updated on AUGUST 29, 2017

Applies to:

PL/SQL - Version 10.2.0.5 and later
Information in this document applies to any platform.

Symptoms

 

Key symptoms:
- ref cursor bind to EXECUTE IMMEDIATE
- works in 10.2.0.4; fails in later versions
- fails with ORA-604 / ORA-1001 or ORA-600: [pevmsql.c: MOVCR:table function] depending on platform

 

CREATE OR REPLACE PACKAGE TEST_INV_CUR AS
   TYPE CURSOR_TYPE IS REF CURSOR;
   PROCEDURE THIS_FAILS(O_RESULT OUT CURSOR_TYPE);
END TEST_INV_CUR;
/

CREATE OR REPLACE PACKAGE BODY TEST_INV_CUR AS
  PROCEDURE THIS_FAILS(O_RESULT OUT CURSOR_TYPE) AS
    I_ROW_START NUMBER := 3;
  BEGIN
    EXECUTE IMMEDIATE ' DECLARE
    TYPE CURSOR_TYPE IS REF CURSOR;
    V_CUR CURSOR_TYPE := :V_CURSOR;
    V_ROW_START NUMBER := :ROW_START;
    BEGIN
      OPEN V_CUR FOR
      SELECT ''ASDF'' RESULT_COL
      FROM USER_SOURCE
      WHERE ROWNUM < V_ROW_START;
    END;'
    USING O_RESULT, I_ROW_START;
  END THIS_FAILS;
END TEST_INV_CUR;


10.2.0.5
==================

SET SERVEROUTPUT ON;
VAR V_MYCUR REFCURSOR;
EXEC TEST_INV_CUR.THIS_FAILS(:V_MYCUR);
PRINT :V_MYCURNo errors.


SQL> BEGIN TEST_INV_CUR.THIS_FAILS(:V_MYCUR); END;


*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms