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

On Mar 13, 10:28 am, Eric Junkermann <e..._at_deptj.demon.co.uk> wrote:
> On 2008-03-13, bfoga..._at_gaports.com <bfoga..._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" xxxxx..._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
> > xxxxx..._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:<xx..._at_xxxxxxx.com> SIZE=47
> > 250 xx..._at_xxxxxxx.com... Sender OK
> >>>> RCPT To:<xxxxx..._at_xxxxxxx.com>
> > 250 xxxxx..._at_xxxxxxx.com... Recipient OK
> >>>> DATA
> > 354 Enter message, end with "." on a line by itself
> >>>> .
> > 250 Message accepted for delivery
> > xxxxx..._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" xxxxx..._at_xxxxxx.com \
>    <oracle_acct_locked.lst
> fi
>
> hth
>
> Eric- Hide quoted text -
>
> - Show quoted text -

I changed the case as you all suggested. That solved the problem. The test now works.

Thank you all for your quick responses and for your suggestions. It would be nice if the solution to all problems were this simple.

Bill Received on Thu Mar 13 2008 - 09:52:30 CDT