How to Simulate a Global Constant in Oracle PL/SQL?

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.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s