Monday, June 27, 2011

SQLPLUS Command using cronjob

Spend three days to figure out how to copy an oracle DB table from a cronjob.

After some intensive surfing for reference online, finally realize what I miss is the declaration.
It is worthy to note down:

For sh:

#!/bin/sh

ORACLE_HOME=/DB/HOME
export ORACLE_HOME
ORACLE_BASE=/DB/HOME
export ORACLE_BASE
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=MYDBID
export ORACLE_SID
NLS_LANG="american_america.we8iso8859p1"
export NLS_LANG

HOME=/HOME/DIR/

PATH=/usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin
export PATH

sqlplus -s username/password@MYDBID @${HOME}/script.sql >> ${HOME}/something.txt

For csh:

#!/bin/csh

setenv ORACLE_HOME /DB/HOME
setenv ORACLE_BASE /DB/HOME
setenv ORACLE_OWNER oracle
setenv ORACLE_SID MYDBID
setenv NLS_LANG "american_america.we8iso8859p1"

set HOME = /HOME/DIR/

setenv PATH /usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin

sqlplus -s username/password@MYDBID @${HOME}/script.sql >> ${HOME}/something.txt

-hayashi