Shell Programming and Scripting

************************************************************************
Shell Script: 
#!/bin/ksh
ODATE=`secure_sqlplus @$./get_daily_odate.sql | tail -2 | head -1 | tr -s ' ' ''`
CHKENDTM=`secure_sqlplus @$./check_batch_endtime.sql \"A\" \"$ODATE\"  | tail -2 | head -1 | tr -s ' ' '' | tail -2c`
************************************************************************
Contents of get_daily_odate.sql: 
select to_char((sysdate-1),'mm/dd/yyyy') from dual
/
************************************************************************
Contents of check_batch_endtime.sql: 
-- This SQL is used to get the end time of the batch for a odate
-- &1 -> batch zone, &2 -> odate
select count(*) from adf_batch_hist
where trunc(odate) = to_date('&2','mm/dd/yyyy') and batch_zone = '&1'
and (start_time is not null or to_char(start_time) <> '')
and (end_time is not null or to_char(end_time) <> '')
/
************************************************************************

$ cat all.sql
DEFINE DO1 = '--'
DEFINE DO2 = '--'
DEFINE DO&1 = ''

SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SPOOL do.sql

PROMPT &&DO1 SELECT 'First SQL' "Exec"
PROMPT &&DO1 FROM DUAL;;

PROMPT &&DO2 SELECT 'Second SQL' "Exec"
PROMPT &&DO2 FROM DUAL;;

SPOOL OFF
SET TERMOUT ON

@do.sql

EXIT;
$ sqlplus -S user/password @all 1

Exec
---------
First SQL

$ sqlplus -S user/password @all 2

Exec
----------
Second SQL

$

$
$ # SQL script with all your queries.
$ cat myqueries.sql
set feed off pages 0 verify off trimspool on
select to_char((sysdate-1),'mm/dd/yyyy') from dual;
select count(*) from emp
where deptno = &1;

$
$ # Korn shell script that connects just once, runs all queries, and spools to a file
$ cat getdatecount.ksh
#!/bin/ksh
sqlplus -s /nolog <<EOF >getdatecount.log
connect test/test
@myqueries.sql 30
exit
EOF

ODATE=`head -1 getdatecount.log`
echo "ODATE = $ODATE"
CHKENDTM=`awk '{if (NR==2){print $1}}' getdatecount.log`
echo "CHKENDTM = $CHKENDTM"

$
$ ./getdatecount.ksh
ODATE = 11/19/2009
CHKENDTM = 6
$
$