Time Activated Functions In SQL

gerTheGreat

I was just wondering if it is possible to create a PL/SQL function that is time activated, eg called at midnight?

Seth Brundle

You mean a stored procedure.
What version of Oracle?
What OS?
You can do this inside the enterprise manager (or via a script)

Also read
http://www.geekinterview.com/question_details/16884
http://sybernet.sri.com/sybase/procedures/SP_HTML_CRON_REGISTER.html

bonkey

gerTheGreat wrote:

time activated functions

I believe they're more commonly called Jobs

DBMS_JOB is most-likely what you're looking to play with here. One tip: Make sure job_queue_processes / job_queue_interval are set up in init.ora. These parameters are version dependant (interval is for 8 & 8i only, IIRC).

You could use OS-dependant scheduling systems like cron in *nix, but DBMS_JOB avoids many potential issues that external schedulers can run into.

jc

gerTheGreat

OK, so i've gotten this far. I've created this function, but it's apparently containing an error, can anyone see the problem?

CREATE OR REPLACE FUNCTION AGE (BIRTH_DATE in DATE)
RETURN NUMBER IS
AGE NUMBER;
BEGIN
AGE := TRUNC(MOUNTHS_BETWEEN(SYSDATE, BIRTH_DATE)/12.0);
RETURN AGE;
END;


(P.S. thanks kbannon and bonkey, I'm not totally sure if i have the admin access to do that but I'll give it a shot once I get the function working)

Seth Brundle

Im kind of surprised that you are running a function at a certain time. Where is the returned value going?

edit: I presume you haven't compiled that finction yet given the typos in it.