[Solved] Unexpected recommendations  

   RSS

0

1/ Why is the recommendation "G-4385: Never use a cursor for loop to check whether a cursor returns data." raised with the following code?

DECLARE
   CURSOR c_dept IS
      SELECT name FROM dept;
BEGIN
   <<my_loop>>
   FOR r_dept in c_dept LOOP
      sys.dbms_output.put_line(dept.name);
   END LOOP my_loop;
END;

2/ Why is the recommendation "G-9002: Local variable should start with 'l_'." raised with the following code?

DECLARE
   c_dept SYS_REFCURSOR;
BEGIN
   NULL;
END;

It seems that SYS_REFCURSOR is not recognised as a cursor.

3/ Why is the recommendation "G-7240: Avoid using an IN OUT parameter as IN or OUT only." raised with the following code?

CREATE OR REPLACE PROCEDURE test AS
   CURSOR c_dept(p_deptno dept.deptno%TYPE) IS
      SELECT name FROM dept WHERE depno = p_deptno
   ;
BEGIN
   NULL;
END;

Default parameter mode is IN so I don't understand the message. I have the same problem with procedure and function parameters with no explicit mode declared. Note that this recommendation is not raised when the same cursor is put in an anonymous block.

BTW, I succeeded to create my own validator for the more advanced naming conventions described in my recent "Naming conventions for PL/SQL" post.

Best,
Philippe

3 Answers
1

1/ Why is the recommendation "G-4385: Never use a cursor for loop to check whether a cursor returns data." raised with the following code?

Because "r_dept" is not used in the loop. The following code should work based on the emp/dept schema and it will not violate the guideline G-4385.

DECLARE
CURSOR c_dept IS
SELECT dname FROM dept;
BEGIN
<<my_loop>>
FOR r_dept in c_dept LOOP
sys.dbms_output.put_line(r_dept.dname);
END LOOP my_loop;
END;

 

@phs

Indeed, my mistake!

0

2/ Why is the recommendation "G-9002: Local variable should start with 'l_'." raised with the following code?

Because the cursor is named "c_dept" and not "l_dept".

@phs

Of course but I would like to get SYS_REFCURSOR considered as cursors (as they are) and prefix their name with "r_".

It is correct when you use the GLP validator. If you use the TrivadisGuidelines3Plus validator then this is a false positive. See https://github.com/Trivadis/cop-validators/issues/5

0

3/ Why is the recommendation "G-7240: Avoid using an IN OUT parameter as IN or OUT only." raised with the following code?

Because no IN nor OUT nor IN OUT is defined for the parameter "p_deptno".

@phs

That was my guess but then the labelling of this recommendation is misleading. It should rather say something like "Avoid implicit IN parameter" or "Always make parameter mode (IN, OUT, IN OUT) explicit". The bad/good example given in the documentation has nothing to do with the fact that the mode is not explicitly declared.

I agree the the naming of the rule is not optimal. However the reason and the example on https://trivadis.github.io/plsql-and-sql-coding-guidelines/v3.6/4-language-usage/7-stored-objects/2-packages/g-7240/ makes it clear. The mode should be set explicitly. See parameter "in_wait" in the bad and good example. In the bad example the mode is missing. In the good example it is defined.

Please Login or Register