UNIX Desktop Questions & Answers

create table CMD_NEW_USER as
Select FD.EMPLOYEE, FD.PASTUSER, .....
FROM REPORTS FS, TASKLIST FD
WHERE FS.EMPLOYEEID = FD.EMPLOYEEID ;

create table CMD_OLD_USER as
Select FD.EMPLOYEE, FD.PASTUSER, .....
FROM REPORTS FS, TASKLIST FD
WHERE FS.EMPLOYEEID != FD.EMPLOYEEID ;

#!/bin/ksh

sqlplus -s /nolog <<-EOF
   connect $USERNAME/$PASSWD@$DB

   spool $LOGFILE

   drop table cmd_new_user;

   create table cmd_new_user as
   Select fd.employee
         ,fd.pastuser
         ,...
     from reports fs
         ,tasklist fd
    where fs.employeeid = fd.employeeid;

   drop table cmd_old_user;

   create table cmd_old_user as
   Select fd.employee
         ,fd.pastuser
         ,...
     from reports fs, tasklist fd
    where fs.employeeid != fd.employeeid;

   spool off

   exit

EOF

exit 0

$ cat my_script
. ~/.profile
ORAENV_ASK=NO
ORAENV=mydb
. oraenv

sqlplus /nolog << !
connect user/pass@db
your DDL goes here;
!

0 23 * * * /path_to_my_script/my_script

. ~/.profile

PATH=/home/oracle/product/10.2.0.8/bin:$PATH

ORAENV_ASK=NO
ORACLE_SID=mydb
. oraenv

ORACLE_SID=mydb  # Optional if you specify a DB name in your connect string, and have a running listener
ORACLE_HOME=/home/oracle/product/10.2.0.8