Problem in UNIX Script that includes SQL*Plus and IF/THEN/FI

From: <bfogarty_at_gaports.com>
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

Original text of this message