Menu
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.
479
To learn how to declare a constant in PL/SQL let's quickly take a look at the syntax. This is our syntax: constant_name CONSTANT datatype (data-width) := value; First you need to give a valid name to your constant followed by keyword CONSTANT that indicates the declaration of a constant in your program.
You use the Const statement to declare a constant and set its value. By declaring a constant, you assign a meaningful name to a value. Once a constant is declared, it cannot be modified or assigned a new value. You declare a constant within a procedure or in the declarations section of a module, class, or structure.
You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage for a value, specify its datatype, and specify a name that you can reference. Declarations can also assign an initial value and impose the NOT NULL constraint.
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.
69
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
