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:
- PL/SQL & SQL Coding Guidelines
- PL/SQL Cop Command Line
- PL/SQL Cop for SonarQube
- PL/SQL Cop for SQL Developer
- PL/SQL Cop Validators
- PL/SQL Analyzer
- PL/SQL Unwrapper
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.
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
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;
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".
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
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
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.