Important Announcement

This forum will be discontinued.

The creation of new topics is disabled.

Please open GitHub issues for the corresponding topics/products in the following Trivadis repositories:

A lot of users have already a GitHub account and the management of the issues is better than in this forum. For example, closing a forum topic is never related to a product change. Closing a GitHub issue for an accepted bug means that the bug is fixed. This should simplify the work for all involved parties.

See this blog post for more information.

Thank you for your understanding.

Notifications
Clear all

[Solved] Unexpected recommendations

8 Posts
2 Users
1 Likes
934 Views
0
Topic starter

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;

 

filou97 Topic starter 21/11/2019 1:45 pm

@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".

filou97 Topic starter 21/11/2019 1:31 pm

@phs

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

Philipp Salvisberg 24/11/2019 3:19 pm

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".

filou97 Topic starter 21/11/2019 1:43 pm

@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.

Philipp Salvisberg 24/11/2019 3:24 pm

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.