Oracle PL/SQL does not support global constants, i.e. constants defined on the schema level.
You can define one in a package, which is typically used when you need to refer to a constant. However, such constant definitions look bulky as you always need to reference package name as a prefix to the constant name.
Deterministic functions allow you to mimic the constant use without sacrificing the performance.
Let’s see an example where we will define a “CONSTANT” that carried the date formatting string (for TO_DATE conversions):
CREATE OR REPLACE FUNCTION date_format RETURN VARCHAR2 DETERMINISTIC AS BEGIN RETURN 'yyyy-mm-dd hh24:mi:ss'; END date_format; /
Now, you can use this function as if it were a globally defined constant:
SET SERVEROUTPUT ON DECLARE v_date DATE; BEGIN v_date:=TO_DATE('2015-11-14', date_format); DBMS_OUTPUT.PUT_LINE(v_date); END; / 14-NOV-15 PL/SQL procedure successfully completed.
According to Oracle documentation, DETERMINISTIC is a “hint that helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result.”
Since our function does not have any parameters, the optimizer will always return the previous (i.e. CONSTANT) result.
Alternatively, you can use RESULT_CACHE option which was first introduced in Oracle 11g:
CREATE OR REPLACE FUNCTION date_format RETURN VARCHAR2 RESULT_CACHE AS BEGIN RETURN 'yyyy-mm-dd hh24:mi:ss'; END date_format; /
The advantage of this method is the cached information can be reused by any session and dependencies are managed automatically.
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.