Greetings!

I wrote this shiny (serenitymovie.com) shell script and wanted to share it with the rest of you. My configuration is Ksh93 on HP-UX v11. Here it goes


#!/usr/bin/ksh
...

# Declare environment parameters, logons, functions...
#----------------------------------------------------#
. $HOME/lib/ETL.lib
...

# Gets TODAY'S DATE from main db instance.
# This will be used to manipulate at runtime the WHERE clause of a SQL \
# statement that gets run.
#------------------------------------------------------#
rcode1=`${ORACLE_HOME}/bin/sqlplus -s <<EOF1
${SRC_ORACLE_LOGON} # DB uname/password in .lib
set heading off;
select to_char(sysdate, 'MMDDYYYY') from dual;
exit;
EOF1`

# Gets YESTERDAY'S DATE from main db instance.
# This will be used to manipulate at runtime the WHERE clause of a SQL \
# statement that gets run.
#-------------------------------------------------------#
rcode2=`${ORACLE_HOME}/bin/sqlplus -s <<EOF2
${SRC_ORACLE_LOGON} # DB uname/password in .lib
set heading off;
select to_char(sysdate-1, 'MMDDYYYY') from dual;
exit;
EOF2`
...

# Sets properties of datum1 & datum1 to rcode1 & rcode2.
# This will initialize the conditions (i.e. dates) in the WHERE clause \
# as well as the file name the query results get spooled out to.
#---------------------------------------------------------#
export rcode1 rcode2 ;
export ORACLE_SID=another_oracle_db_instance_name ;
export filler="080000" ;
export datum1="$rcode1$filler" ; export datum2="$rcode2$filler"
...
# Set properties for file management.
#----------------------------------------------------------#
export suffix=".uld" ; export label1="SALES_REVENUE_"

# Download of SALES REVENUE data.
# Exporting `salesrevdatafname` will allow building dynamically \
# the output file name with a timestamp I obtained earlier.
#----------------------------------------------------------#
export salesrevdatafname="$label1$rcode3$suffix"
${ORACLE_HOME}/bin/sqlplus -s <<! > $LOG/sql.og
${TGT_ORACLE_LOGON} # DB uname/password in .lib
SET ARRAYSIZE 5000 COLSEP '|'
spool $DATA_TGT/$salesrevdatafname;
SELECT ... FROM SALES
WHERE trx_start_time >= to_date('$datum2','MMDDYYYYHH24MISS') AND
trx_start_time < to_date('$datum1','MMDDYYYYHH24MISS');
spool off;
exit;
!
...


That's it. The main reason why I am posting this is that I went thru a lot of trial & error to get what I was looking for (this). Who knows, this code may be of help to someone with similar need.

NB: I was able to tweak this code to make it run on IBM DB2 on AIX as well.

At the risk of sounding like a pompous *ss...This bit of code is in the public domain. Modify it as you please.

Nice job especially since it works so well for you.

One question. Why are you exporting every one of your variables?

By the way, you can give yourself a bit of a short cut and avoid the unnecessary second sqlplus session by using a little different technique. This one uses an array.

or, with only one database connection:

Three simple, really not fancy, reasons:

1) Configuration management
1.A) I first code/test my work using the env variables of my dev/qa server/environment such Oracle DB user name/password, Oracle TNS name, directory name(s) etc.
1.B) One my qa is completed, I push my shell script to my prod server. The variables still have identical names as in `dev`; however, I can/do change the values of each variable to match my `prod` environment e.g. Oracle DB user name/password, Oracle TNS name, directory name(s) etc.

2) Security
2.A) The shell scripts have a chmod value which allows anybody that's not me (ie. my Unix id) to read the code. However, the chmod value of my ETL.LIB file make it so that it can only view/modified by me and the root user. Stuff like passwords, file/directory names etc is hidden from most people.

3) Simplicity
3.A) I have 214 shell scripts like the one I posted. Some are wrappers to SQL*Loader jobs, others to SQL*scripts...It is a lot easier for me to manage changes by editing one single file (ETL.LIB...as opposed to each one of them) whenever our DBA feels like changing passwords, TNS names or when our sysadmin decides to mount a new file system etc...

That's about it.

PS: Thanks for the kind comments.

I like this. it is really clever example since you're able to accompliish the same thing as me in one single shot.

Thanks for your input!!

Alan