asked on

Oracle Global Constant Without A Package

Hello,
I'm converting a really complicated Sybase file dump stored proc to Oracle and have decided the only reasonable way to recreate the logic is to recreate the sybase functions as wrappers for their Oracle equivalents, so I don't have to mess with the original code (which in some cases has about 20 levels of nested substrings, charindex's and other horrors of paranthesis and commas).

I'd like to recreate the global constants of sybase, for example in this sybase function:

DATEDIFF(Day, getdate(), '1/1/2001')

I'd like to make a global variable called DAY that I can use anywhere that just passes the string 'DAY' so that I don't have to edit the original code. I know I can make a package of global variables and have CONST.DAY but that means replacing which means I might as well just put 'DAY' anyways. Is there a way to make a global constant that I can refence anywhere without adding anything onto it?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sean Stuber

I'd create your function as deterministic and result cache to try to minimize invoking the pl/sql and suffering context switching.

Plus, a constant is deterministic so no reason to not declare it such.

following slightwv's lead, something like this...

CREATE OR REPLACE FUNCTION day
RETURN VARCHAR2
DETERMINISTIC
RESULT_CACHE
IS
BEGIN
RETURN 'DAY';
END;