Solved

How can I schedule a Oracle Procedure in Unix

Posted on 2002-06-18
6
301 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hello fellow BSD lovers, I've created a patch process for patching openjdk6 for BSD (FreeBSD specifically), although I tried to keep all BSD versions in mind when creating my patch. Welcome to OpenJDK6 on BSD First let me start with a little …
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now