How can I run a time-based stored procedure that is going to check a specific table each X minutes, find any record that is older than the same X minutes and delete it (or run an ASP page from the Web server instead)? I want this table to be updated each X minutes and hold always-fresh records.
Write your stored procedure to perform the actions as desired, then schedule it to run every X minutes using the DBMS_JOB package.
Here's an example (pretty much straight out of the Oracle Supplied Packages Reference):
declare v_jobno number; begin dbms_job.submit(v_jobno, 'my_stored_proc;', sysdate, 'sysdate + (10/(24*60))'); dbms_output.put_line('Your job was submitted and assigned the job #: ' || v_jobno) ; end ; /This would run your procedure (called my_stored_proc) immediately (sysdate) and then schedule it to run continuously at a 10 minute interval ('sysdate + (10/(24*60)) ').
See the Oracle Supplied Packages Reference for more details on the use of DBMS_JOB.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle stored procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.