Re: CodeXpert - why reports CURSOR violations?

Andre Vergison <[email protected]> Wed, 18 Apr 2012 12:10:06 PDT
Newsgroups	gmane.comp.db.oracle.toad.free
Message-ID	<3257120.75371334776237671.JavaMail.SYSTEM@alvqcfw02>
 Message from: avergison

Hello Andreas,

A few code analysis rules use older legacy code which often has flaws. We are in the process of replacing old code over time. We already did such for the majority of the rules by introducing XPath expressions. It will sure take several months to finish all kinds of analysis, including cursor analysis. Thanks for understanding, we do the best we can.

Andre


_______________________________________

Historical Messages 


Author: Andre Vergison
Date: Wed Apr 18 12:10:06 PDT 2012
Hello Andreas,

A few code analysis rules use older legacy code which often has flaws. We are in the process of replacing old code over time. We already did such for the majority of the rules by introducing XPath expressions. It will sure take several months to finish all kinds of analysis, including cursor analysis. Thanks for understanding, we do the best we can.

Andre

__

Author: Andreas Schmid
Date: Tue Apr 10 04:26:37 PDT 2012
Can anyone tell me if the above described problem still occurs the newest version (11.0) by using the provided code example?

Thank you very much ...

__

Author: Andreas Schmid
Date: Tue Apr 03 07:50:32 PDT 2012
Hello,

I am wondering why I get following CURSOR violations on the simple code example below:

* three times &quot;Cursor - Ensure that any local cursors you are referencing have actually been opened&quot;
** (18,16)
** (20,13)
** (22,19)
* once &quot;Cursor - Confirm that the cursor you are referencing has been opened&quot;
** (12,14)

{code}
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
&nbsp;&nbsp; CURSOR cursor1
&nbsp;&nbsp; IS
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT&nbsp;&nbsp; 1 FROM DUAL;

&nbsp;&nbsp; PROCEDURE PROC_TEST
&nbsp;&nbsp; IS
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val_i&nbsp;&nbsp; NUMBER (10);
&nbsp;&nbsp; BEGIN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF NOT cursor1%ISOPEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPEN cursor1;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOOP
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FETCH cursor1 INTO&nbsp;&nbsp; val_i;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF cursor1%NOTFOUND
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLOSE cursor1;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXIT;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /* ... some further code ... */
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END LOOP;
&nbsp;&nbsp; END PROC_TEST;
END PKG_TEST;
/
{code}

Can anybody help me figuring out this?

IMHO it is necessary to ask here if the cursor is already opened due to its declarations in package scope and the possible exception for opening an already open cursor. 
For sure one can fix this situation if the cursor is moved to procedure scope but that is not possible because it is reused at another place (this short example just does not contain this).

I am using Toad 9.7.2.5 ...

I very much appreciate your help, thank you.

__
_______________________________________