Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Function Error

479424Dec 29 2005 — edited Dec 29 2005
I'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 26 2006
Added on Dec 29 2005
4 comments
378 views