Function

Can someone help me in writing the following function :

FN_IS_SYSDATE_TICKING common function

Purpose: Determine if the sysdate is still ticking or frozen

Input parameters : none;

Returns: boolean true if the SYSDATE in the normal “ticking state
Or boolean false if the SYSDATE has been frozen.
This function should never return a null value. Also, any exceptions should neither be reported nor raised.
Note: The boolean true/ false value returned allows this function to be used in a pl/sql if statement (i.e. IF fn_is_sysdate_ticking then…)

Processing:

Select value, isspecified from V$SPPARAMETER
Where name = ‘fixed_date’.

If isspecified = ‘FALSE’
Or (isspecified = ‘TRUE’ and value = ‘NONE’)
Then (the sysdate clock is “ticking”)
Return boolean TRUE
Othewise (sysdate clock is frozen)
Return boolean FALSE
Should always return boolean true on an error in the function. (i.e. via the pl/sql exception block).

 

CREATE OR REPLACE FUNCTION FN_IS_SYSDATE_TICKING (
ps_name IN V$SPPARAMETER.name%TYPE DEFAULT 'fixed_name'
) RETURN BOOLEAN
IS
ls_value V$SPPARAMETER.value%TYPE;
ls_issspecified V$SPPARAMETER.issspecified%TYPE;
BEGIN
SELECT value, issspecified
INTO ls_value, ls_issspecified
FROM V$SPPARAMETER
WHERE name = ps_name;
--WHERE name = fixed_date';

IF ls_issspecified = 'FALSE' OR
(ls_isspecified = 'TRUE' and ls_value = 'NONE') THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN TRUE;
END FN_IS_SYSDATE_TICKING;

Tried the above code.

Got the following error message :

PLS-00201: identifier 'V$SPPARAMETER' must be declared