Where do you normally store your PL/SQL constants? On the package-body level? In the specification? I've also seen some people holding constants in a specialized package just for constants. What are best practices in this area?
Thanks.
+3
A:
In many cases, you want to keep them in the specification so other packages can use them, especially as parameters when calling functions and procedures from your package.
Only when you want to keep them private to the package, you should put them int the body.
Having a package just for constants might be a good idea for those constants that are not related to any piece of code in particular, but relevant for the whole schema.
ammoQ
2010-02-17 09:15:29
+1
A:
I would prefer the constants to be, by default, in the package body unless you use the constant as a parameter value for one of your public package procedures/functions or as a return value for your functions.
The problem with putting your constants in the package specification is that if you need to change the constant's type, other packages might fail that use the constant because it was just there. If the constant was private in the first place, then you don't need to perform an impact analysis for each change.
If you need to store contants like default language or stuff like that, then I would encapsulate those contants in functions like get_default_language etc. and keep the constants private.
Diederik Hoogenboom
2010-02-17 10:21:10
+1
A:
I'd have a concern about having "one package to rule the constants" because package state -- constants, variables, and code -- get cached in the user's PGA at first invocation of any public variable or package. A package constant, if public, should be scoped to the package, and used by only by the methods of the package.
A constant whose scope spans packages should be in a code table with a description, joined in as required. Constant's aren't and variables don't, after all. Having a key-value-pair table of "constants" makes them all public, and makes changing them dynamically possible.
Adam Musch
2010-02-17 17:20:59
+1
A:
One downside to having constants in a package body or spec is that when you recompile the package, any user sessions that had the package state in the PGA would get ORA-04068. For this reason, in one large development environment we adopted the convention of having a separate spec-only package to hold the constants (and package globals if any) for each package. We'd then impose a rule saying that these spec-only packages were only allowed to be referenced by their "owning" package - which we enforced at code review. Not a perfect solution, but it worked for us at the time.
For the same reason, I'd never recommend one-constant-package-to-rule-them-all because every time someone needs to introduce a new constant, or modify an existing one, all user sessions get ORA-04068.
Jeffrey Kemp
2010-02-18 06:05:52
Epilogue for completeness: best practice normally is to define everything as close to their use as possible: if the constant is only used by one procedure, define it in that procedure; if the constant is only used in a package body, define it in the package body (except for the ORA-04068 problem noted above - which becomes less of an issue in 11gR2, it should be mentioned, if editions are used).
Jeffrey Kemp
2010-02-22 05:46:11
+1
A:
For our application, all constants are in a table. A simple function is used to extract them. No problem with recompilation, ORA-04068, ...
Olof
2010-02-18 07:08:44