• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5820
  • Last Modified:

How to automate sql script using cron job

Dear experts -
I have to automate a sql script to run every day. I would like to know the various ways. I would prefer to do it using CRON. Please advice. Kind of urgent

regards
0
akp007
Asked:
akp007
3 Solutions
 
chedgeyCommented:
What does your SQL script do?

If it a simple SQL script then the Oracle Job Scheduler is your best bet. See:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#sthref6598

If the is controlled from a shell or Perl script then you can schedule it from CRON using by editiing Oracle's CRONTAB using the command "crontab -e".

Regards

chedgey
0
 
akp007Author Commented:
my script involves read from db and write to a table. I have an alter statement to change the optimizer setting. So it has alter,select and insert. I want to use shell for that. can you please provide me a some kind of script or idea, I can take it from there. Thanks for the fast response
regards
0
 
Jinesh KamdarCommented:
Create a SQL script with ur DML / DDL statements. Next create a shell script that invokes the SQLPLUS with the SQL script. Another option is to create a stored procedure for your DDL / DML statements and schedule a DBMS_JOB to run it at periodic intervals.
0
 
johnsoneSenior Oracle DBACommented:
Below is a sample script that you could put into cron.  Remember that cron does not set the environment so you have to do it in the script.
#!/bin/sh
 
ORACLE_SID=<sid>
ORACLE_HOME=<ora_home>
PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_SID ORACLE_HOME PATH
 
${ORACLE_HOME}/bin/sqlplus <user>/<pass> << END
-- Statements go here
exit
END

Open in new window

0
 
akp007Author Commented:
Thanks. I got an idea and now I am implementing it
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now