Solved

How can I schedule a Oracle Procedure in Unix

Posted on 2002-06-18
6
303 Views
Last Modified: 2010-08-05
I would like to run a procedure in Oracle once in a month.
How can I do this
0
Comment
Question by:syathm
  • 4
6 Comments
 
LVL 20

Expert Comment

by:tfewster
ID: 7089163
Use cron:

As Oracle, do `crontab -e`
Insert a line:
minute hour monthday * * /path/to/script

Remember that cron doesn't use your .profile etc, so ensure the script explicitly sets all paths & variables that it will need.

(man cron & man crontab for the full info :)

0
 
LVL 20

Expert Comment

by:tfewster
ID: 7089240
If the oracle user doesn't have permission to use cron, you can add them to cron.allow OR have root's crontab kick off the command with
minute hour monthday * * su - oracle -c /path/to/script

If your script doesn't send stdout & stderr to a log file itself, you can redirect the output within cron; Otherwise, any output will be sent to the owner of the cron job as a mail message. (I hate it when that happens, as noone ever reads their unix mail file, and they just grow forever ;-)
0
 
LVL 20

Expert Comment

by:tfewster
ID: 7103627
syathm, did that answer your question? If you need more explanation of cron, please let me know.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Accepted Solution

by:
JYoungman earned 50 total points
ID: 7116311
A more effective way of doing this (in Oracle 8 and above) is to use the built-in DBMS_JOB feature.  This is a package which manages the periodic calling of a PL/SQl procedure.   you might use it like this :-

create procedure my_monthly_job as
BEGIN
   name varchar2(100);
   -- ... lots of good stuff here ...
END
/



-- Now set the job up (you only need to do this once)
DECLARE
  jobno NUMBER;
BEGIN
  dbms_job.submit(jobno, 'my_monthly_job',
      SYSDATE, ADD_MONTHS(SYSDATE, 1) );
END;
/

This mechanism is better because there is no need to set up ORACLE_HOME, TWO_TASK, and so on, and you don't have to worry about putting passwords into files, setting up pexternally-dentified accounts, and so forth.  The DBMS_JOB package also provides management features.

For more information about DBMS_JOB, please see

Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
A76936-01

0
 
LVL 20

Expert Comment

by:tfewster
ID: 7832966
No comment has been added lately, so it's time to clean up this Topic Area.
I will leave a recommendation for this question in the Cleanup topic area as follows:

- Answered by JYoungman

Please leave any comments here within the next 7 days

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

tfewster (I don't work here, I'm just an Expert :-)
0
 

Expert Comment

by:modulo
ID: 7908941
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
I promised to write further about my project, and here I am.  First, I needed to setup the Primary Server.  You can read how in this article: Setup FreeBSD Server with full HDD encryption (http://www.experts-exchange.com/OS/Unix/BSD/FreeBSD/A_3660-S…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question