Oracle Function Error
479424Dec 29 2005 — edited Dec 29 2005I'm trying to convert a unix time into a current date/time and found this program to do so:
CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
oracle_date DATE;
BEGIN
IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too large for 32 bit limit'
);
ELSIF unixts < min_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too small for 32 bit limit' );
ELSE
oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
END IF;
RETURN (oracle_date);
END;
However when I compile it I get an error message saying the NUMTODSINTERVAL identifier must be declared. I have only begun using PL/SQL however this would lead me to believe that I don't have access to this function or there is something I am ommiting. My account has only just been set up so perhaps I don't have the right privelages to use some functions in oracle? Would appreciate any help,
Simon