Problem in UNIX Script that includes SQL*Plus and IF/THEN/FI
Date: Thu, 13 Mar 2008 06:49:49 -0700 (PDT)
Message-ID: <47ef2d1b-56aa-4c8f-9d96-448dbe74c4ec@13g2000hsb.googlegroups.com>
I wrote a UNIX script that executes a SQL statement via SQL*Plus,
writes output to a spool file, then checks the spool file for a length
<> 0. If the length is > 0 then an email message is sent to me showing
the contents of the spool file.
The IF/THEN/FI that is to check the file length is not working. For it
I get messages:
./oracle_acct_locked.sh[20]: IF: not found.
./oracle_acct_locked.sh[21]: THEN: not found.
./oracle_acct_locked.sh[23]: FI: not found.
The SQL*Plus statement works.
The writing of the spool file works.
The sending and receiving of the email works, except that the email is
sent even if the file length = 0.
Why does the IF/THEN/FI fail?
The contents of the UNIX script, and the messages produced by its execution, are shown below.
Thank you,
Bill
$ pg oracle_acct_locked.sh
#!/bin/ksh
# This script will check for Oracle users whose account has been
"locked".
# The lock occurs after ten failed logon attempts.
# An email message will be sent to the Oracle DBA listing locked
accounts.
# In no accounts are locked then an email will not be sent.
${ORACLE_HOME}/bin/sqlplus -s <<EOF
/ as sysdba
set feedback off
set pagesize 0
set trimspool on
spool oracle_acct_locked.lst
SELECT username
FROM dba_users
WHERE account_status LIKE 'LOCKED%'
ORDER BY username;
spool off
EOF
#Send messages about "locked" Oracle accounts
IF [ -s oracle_acct_locked.lst ]
THEN
mail -v -s "ACCT LOCKED" xxxxxxxx_at_xxxxxx.com
<oracle_acct_locked.lst
FI
$ ./oracle_acct_locked.sh
./oracle_acct_locked.sh[20]: IF: not found.
./oracle_acct_locked.sh[21]: THEN: not found.
Null message body; hope that's ok
xxxxxxxx_at_xxxxxxx.com... Connecting to xxx.xxxxxxx.com via relay...
220 xxx.xxxxxxx.com ESMTP Service (Lotus Domino Release 7.0.3) ready
at Wed, 12 Mar 2008 11:58:38 -0400
>>> EHLO xxxxxxx.com.
250-xxx.xxxxxxx.com Hello xxxxxxx.com. ([xxx.xxx.xxx.x]), pleased to
meet you
250-HELP
250-SIZE 15360000
250 PIPELINING
>>> MAIL From:<xxxxx_at_xxxxxxx.com> SIZE=47
250 xxxxx_at_xxxxxxx.com... Sender OK
>>> RCPT To:<xxxxxxxx_at_xxxxxxx.com>
250 xxxxxxxx_at_xxxxxxx.com... Recipient OK
>>> DATA
354 Enter message, end with "." on a line by itself
>>> .
250 Message accepted for delivery
xxxxxxxx_at_xxxxxxx.com... Sent (Message accepted for delivery)
Closing connection to gpa.gaports.com
>>> QUIT
221 xxx.xxxxxxx.com SMTP Service closing transmission channel
./oracle_acct_locked.sh[23]: FI: not found.
Received on Thu Mar 13 2008 - 08:49:49 CDT