PL/SQL: Is a CURSOR always Dynamic SQL ?? (pls-00455)

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 28 Jul 2005 02:21:01 -0700
Message-ID: <1122542461.910351.262400@g44g2000cwa.googlegroups.com>

Hi there.

I have a Oracle 9i database here.

When I try to open a cursor FOR a cursor variable, i get the error: <PLS-00455: cursor 'X_SMDACUR' cannot be used in dynamic SQL OPEN statement>

Searching the net, I could only find examples where this error occurs in connection with Dynamic SQL, but I do not use dynamic sql, but a static defined cursor.

Heres my testing code: (see comment PLS-00455)

• CODE ----
  • Package Def, OK CREATE OR REPLACE PACKAGE TestPackage IS PROCEDURE Test_Procedure ( I_PAR1 IN VARCHAR2 DEFAULT NULL ); END TestPackage;
  • Package Body: CREATE OR REPLACE PACKAGE BODY TestPackage IS
    /* A record type */
    TYPE rec_TestRecord IS RECORD (Test1 VARCHAR2(100) );

/* A strong cursor var type returning the record */
  TYPE curtype_Test IS REF CURSOR RETURN rec_TestRecord;

/* A static(???) cursor */

  CURSOR cu_testCursor RETURN rec_testRecord   IS

     select 'This is a test' from dual ;

/* A procedure trying to open the static cursor for the cursor var */

  PROCEDURE Test_Procedure ( I_PAR1 IN VARCHAR2 DEFAULT NULL )   IS

          X_TestCursor curtype_Test;
  BEGIN

       /****  this is OK ****/
  	   OPEN X_TestCursor FOR select 'hello' from dual;
	   CLOSE X_TestCursor;

	   /**** this results in PLS-00455 ! ****/
	   OPEN X_TestCursor FOR cu_testCursor;
	   CLOSE X_TestCursor;

  END;
END TestPackage;
/

• END CODE ----

... ideas anyone?

thanks!

best regards,
Martin Received on Thu Jul 28 2005 - 04:21:01 CDT