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

On 2008-03-13, bfogarty_at_gaports.com <bfogarty_at_gaports.com> wrote:
> 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.
>

This is because the shell commands are case sensitive, you should have:

if [ -s oracle_acct_locked.lst ]
then
  mail -v -s "ACCT LOCKED" xxxxxxxx_at_xxxxxx.com \    <oracle_acct_locked.lst
fi

hth

Eric Received on Thu Mar 13 2008 - 09:28:37 CDT