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.
We're starting a new project and are in the phase where we are defining coding standards and guidelines and check our existing code against those. That's where we are confused regarding guidelines G-1050 'Avoid using literals in your code.' and G-7230 'Avoid declaring global variables public.'.
During the re-write of our code to meet G-1050, we created - as suggested - a package containing all our application-wide constants that will be used as a library.
But this new package obviously does not meet the G-7230 check, since it exactly doing what the guideline tells not to.
The G-1050 guideline mentions 'If these constants should be used in SQL too it is good practice to write a deterministic package function for every constant.' ... So we could re-write our library-package with constants so that the constants are private but they are publicly exposed by writing a deterministic get-function for each of them. Doing this, all guidelines are met. But I ask myself what's the added value of hiding a constant value behind a getter-function?
In the meantime I wrote the blog post I mentioned before. See https://www.salvis.com/blog/2019/12/13/constants-vs-parameterless-functions/ .
This makes clear that declaring constants in a package specification is okay. The Guideline G-7230 is also correctly formulated, but the example is misleading. I have opened an issue on GitHub.
Regarding G-1050 there was a related discussion on GitHub where the guidelines are maintained.
Regarding G-7230: If you define constants or variables as part of the package body, then this guideline is not violated. The bad and good examples on https://trivadis.github.io/plsql-and-sql-coding-guidelines/v3.6/4-language-usage/7-stored-objects/2-packages/g-7230/ should make it clearer.
But I ask myself what's the added value of hiding a constant value behind a getter-function?
You can use the function in a SQL statement outside of PL/SQL. This is not possible for constants or variables. This helps to develop complex SQL statements, because you can run them directly without changing constants or variables to literals. However, this has some other drawbacks. I plan to write a blog post about this issue. Soon. ;-)