Strange Error from Codexpert.

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange Error from Codexpert.

deftones209
Hi,
when I analyse this code with codexpert:

PROCEDURE vi_insert_log (
      i_info        IN   VARCHAR2,
      i_operation    IN   VARCHAR2,
      i_requestid   IN   VARCHAR2
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO BUNDLE_BENEFITS_PROCESS_LOG
                  (counter, info, datecreated,operations,
                    hn_requestid
                  )
           VALUES (BUNDLE_BENEFITS_PROCESS_LOG_sq.NEXTVAL, i_info, SYSDATE,i_operation,

                    i_requestid
                  );

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
         DBMS_OUTPUT.put_line ('Error while logging--' || SQLERRM);
   END;


Toad codexpert is complaining on the like "DBMS_OUTPUT.put_line.." giving me an error that "Cursor - Avoid procedure or function calls between a SQL operation and an implicit cursor test".

To be honest I cannot see the problem, what is wrong with that code? There is no %FOUND or SQL%ROWCOUNT.
Can you give me a Hint?
Thank you very much!!
Manuel

Reply | Threaded
Open this post in threaded view
|

RE: Strange Error from Codexpert.

Gregory Liss
What version of Toad are you using?  For Toad 11, we completely reworked the technology behind CX and renamed it Code Analysis.  For your code, using All Rules to do the analysis, I get the following violations:

      Rule 2126: Encapsulate all procedures and functions in packages. - [Maintainability, PLSQL, Warning] (1: 11)
      Rule 5910: Parameters should start with P_, end with _i or _o or _io, and if _o then NOCOPY hint is required. - [Readability, PLSQL, Warning] (2: 7)
      Rule 5910: Parameters should start with P_, end with _i or _o or _io, and if _o then NOCOPY hint is required. - [Readability, PLSQL, Warning] (3: 7)
      Rule 5910: Parameters should start with P_, end with _i or _o or _io, and if _o then NOCOPY hint is required. - [Readability, PLSQL, Warning] (4: 7)
      Rule 3017: Consider calling DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in your exception section. - [Program Structure, PLSQL, Warning] (19: 4)
      Rule 3001: Avoid using a WHEN OTHERS clause in an exception section without any other specific handlers. - [Maintainability, Exception, Information] (20: 12)
      Rule 5004: Use of NULL statements. - [Program Structure, NULL, Information] (22: 10)
      Rule 3004: Avoid masking exceptions with use of NULL statement in an exception handler. - [Program Structure, Exception, Information] (22: 10)
      Rule 2007: Avoid direct calls to DBMS_OUTPUT.PUT_LINE. - [Maintainability, PLSQL, Warning] (23: 10)
      Rule 4602: Avoid use of literals in non-declarative parts of the program. - [Maintainability, Literal, Information] (23: 32)
      Rule 3013: Use DBMS_UTILITY.FORMAT_ERROR_STACK in place of SQLERRM. - [Maintainability, PLSQL, Warning] (23: 59)
      Rule 4404: Avoid unlabeled END of program units, packages or types. - [Readability, Label, Information] (24: 4)

For your line in question, I get two violations:




I highly recommend that you upgrade to the latest version of Toad if you are using CX.  The rewrite was long in coming but offers several features that make using it much easier and faster.

Greg



-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of deftones209
Sent: Thursday, October 18, 2012 10:44 AM
To: [hidden email]
Subject: [toad] Strange Error from Codexpert.

Hi,
when I analyse this code with codexpert:

PROCEDURE vi_insert_log (
      i_info        IN   VARCHAR2,
      i_operation    IN   VARCHAR2,
      i_requestid   IN   VARCHAR2
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO BUNDLE_BENEFITS_PROCESS_LOG
                  (counter, info, datecreated,operations,
                    hn_requestid
                  )
           VALUES (BUNDLE_BENEFITS_PROCESS_LOG_sq.NEXTVAL, i_info, SYSDATE,i_operation,

                    i_requestid
                  );

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
         DBMS_OUTPUT.put_line ('Error while logging--' || SQLERRM);
   END;


Toad codexpert is complaining on the like "DBMS_OUTPUT.put_line.." giving me an error that "Cursor - Avoid procedure or function calls between a SQL operation and an implicit cursor test".

To be honest I cannot see the problem, what is wrong with that code? There is no %FOUND or SQL%ROWCOUNT.
Can you give me a Hint?
Thank you very much!!
Manuel



------------------------------------

Yahoo! Groups Links





Picture (Device Independent Bitmap) 1.jpg (868 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Strange Error from Codexpert.

deftones209
Hi Greg,
thanks for the quick answer.
We are using 9.7.2.5. Unfortunately Upgrade is not that easy, you know, big company with company policies.

Now maybe I add one more detail.
This part of the code is indeed part of a muuuch bigger procedure.
If I copy out only the codelines I have posted and run CX only on those lines, I also do not get that warning anymore.
If I run in on the complete procedure, then I get that error to that line ( it says something like 223 occurencies).

Which leads me to two hypothesis:
- The problem is there but the rowline is wrong.
- Toad has some parsing issues due to the size of the package, and indeed there is no such Cursor issue at all.

What do you think?
Thanks a lot
manuel

Reply | Threaded
Open this post in threaded view
|

RE: Re: Strange Error from Codexpert.

Gregory Liss
It could be one of many things.  That's one of the main reasons that the entire technology was reworked last year.  If this is a technology that you find valuable, You might want to consider downloading the trial version and giving the new technology a test drive.  Some of those new features might just convince management that it's time to upgrade.

Greg

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of deftones209
Sent: Thursday, October 18, 2012 10:59 AM
To: [hidden email]
Subject: [toad] Re: Strange Error from Codexpert.

Hi Greg,
thanks for the quick answer.
We are using 9.7.2.5. Unfortunately Upgrade is not that easy, you know, big company with company policies.

Now maybe I add one more detail.
This part of the code is indeed part of a muuuch bigger procedure.
If I copy out only the codelines I have posted and run CX only on those lines, I also do not get that warning anymore.
If I run in on the complete procedure, then I get that error to that line ( it says something like 223 occurencies).

Which leads me to two hypothesis:
- The problem is there but the rowline is wrong.
- Toad has some parsing issues due to the size of the package, and indeed there is no such Cursor issue at all.

What do you think?
Thanks a lot
manuel



------------------------------------

Yahoo! Groups Links