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

From: <>
Date: Thu, 13 Mar 2008 06:49:49 -0700 (PDT)
Message-ID: <>

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:
./[20]: IF: not found.
./[21]: THEN: not found.
./[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,

$ pg

# 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 ]
    mail -v -s "ACCT LOCKED" <oracle_acct_locked.lst

$ ./
./[20]: IF: not found.
./[21]: THEN: not found.
Null message body; hope that's ok Connecting to via relay... 220 ESMTP Service (Lotus Domino Release 7.0.3) ready at Wed, 12 Mar 2008 11:58:38 -0400
>>> EHLO Hello ([]), pleased to meet you
250-SIZE 15360000
>>> MAIL From:<> SIZE=47 250 Sender OK
>>> RCPT To:<>
250 Recipient OK >>> DATA
354 Enter message, end with "." on a line by itself >>> .
250 Message accepted for delivery Sent (Message accepted for delivery) Closing connection to
>>> QUIT
221 SMTP Service closing transmission channel
./[23]: FI: not found.
Received on Thu Mar 13 2008 - 08:49:49 CDT

Original text of this message