Multiple SQL Commands

Hello Experts !!!

Im new to this so, please bear with me !

I have the following 2 sql statements

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 ;

Now, here are the challenge's

1. I need to create a shell wrapper so that the above two sql create table commands run successfully by connecting to the correct database.
2. Run this everyday at 11 PM so that the next day this is done.

Any and all help is sincerely appreciated !

Thanks,
Lee

You can do this a couple different ways. Here's one that will do all the work in the script.

#!/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

You might want to throw in a "drop table" somewhere before the "create table"

Assuming Oracle:

In your script source your .profile, set ORAENV_ASK to NO, set your ORACLE_SID to that of the database you're planning to connect, and then source "oraenv".

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

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

As for the "11 PM" part, set up a cronjob.

0 23 * * * /path_to_my_script/my_script

Scottn & Jsmitstl,

Thanks a bunch on your responses and Im thankful for those.

Scottn, You are right in that this is oracle that we are talkin about bu you lost me there on the following and Im not sure as what you mean here.

In your script source your .profile, set ORAENV_ASK to NO, set your ORACLE_SID to that of the database you're planning to connect, and then source "oraenv".

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

Now, Im reading something about the Cron and I will certainly need some more time for what you have mentioned in here.

Thanks,
Lee.

Hi.

I mean that when you run something from cron your environment is not set up as when you normally "log in".

You need to "set it up" yourself.

Sourcing (loading) your .profile is normally a good place to start

. ~/.profile

If that doesn't give you all you need, then:

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

(or wherever your Oracle software "lives")

If you want to use SQL*Plus, then you need to set your Oracle environment. oraenv does this for you:

ORAENV_ASK=NO
ORACLE_SID=mydb
. oraenv

or:

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