if then else script wont work with oracle command

[Dave Chamberlin]

Hello,
I am modifying an old bourne shell script in which there is a part that does:
sqlplus xxx/yyy << EOF
select stuff from mytable
/
EOF

This works fine, until I put it into a conditional statement:
if [ $1 = "PROD" ]
then
sqlplus xxx/yyy << EOF
select stuff from mytable
/
EOF
else
sqlplus xxx/yyy << EOF
select otherstuff from mytable
/
EOF
fi

this fails with 'else' is not expected
why does this not work?

[Biswajit Tripathy]

There is no (obvious) reason why it should behave
differently. Run the script with debug mode
(i.e ksh -x script) and see if it helps you.

- Biswajit

[Julio Yamawaki]

Hi,

I made this test:

A=y
if [[ $A = "x" ]]
then
sqlplus system/manager <select * from v\$backup where status = 'ACTIVE'
/
exit;
EOF
else
sqlplus system/manager <select tablespace_name from dba_tablespaces
/
exit;
EOF
fi

And it worked well.
I think you have some "strange" character, like backspace, inside your command.

Regards,

[Peter Godron]

Dave,
try running your shell :
sh -x shell.sh PROD
This should help you debug what is happening.

Perhaps you should check your EOF labels and rename the second set of EOFs to something else, add exits to the sqlplus statements and add -s after sqlplus to suppress banner.

Regards

[renarios]

Hi Dave, try the POSIX syntax like Julio wrote. Just put the second heredoc at the beginning of the line, else it will not work.
If you like to use indentation, use a minus sign i.e.:
<<-EOF
...
EOF
to ignore leading tabs

For security reasons, try sqlplus << EOF
system/manager
EOF
or sqlplus /nolog <connect system/manager
EOF
in stead of sqlplus system/manager << OEF
...
EOF, because with an ps command the login/password will be shown

You might try another construction:
case $1 in
PROD)
stmt="select 'blah' from dual; "
;;
TEST
stmt="select 'halb' from dual"
;;
esac

sqlplus << EOF
connect system/manager
$stmt;
EOF

That's faster, because case is a shell built-in

Hope this helps,

Renarios

[Etienne Roseau]

hi Dave,
i would check if no "strange characters" is polluing the script or a coma somewhere. that's often the problem !
don't you have a line error seeing by the shell ( "error line xx") ??
otherwise the debug will work fine for a command error or an in/out-put error
HTH
E.

[Steve McKenna_3 · Accepted solution]

Are you indenting the EOF within the if statement? EOF must be the first characters on the line for it to end the sql command.

[Steve McKenna_3]

One more thought.. and alternative way to do this..


if [ $1 = "PROD" ]
then
stuff="whatever"
else
stuff="whatever else"
fi

sqlplus xxx/yyy << EOF
select ${stuff} from mytable
/
EOF

[A. Clay Stephenson]

Let me suggest a Plan B that avoids all the possible 'heredocs' syntax differences in shells that only get more complicated if you need to add a 3rd or 4th conditional branch.


#Create a temporary file
TDIR=${TMPDIR:-/var/tmp}
S1=${TDIR}/X${$}_1.sql

trap '{eval rm -f ${S1}' 0 1 2 15
# Trap forces removal of temp file upon exit
# or interrupt

#Now simply use echo satements to build up #the sql file

rm -f ${S1} # null the file
if [ "${1}" = "PROD" ]
then
echo "select stuff from mytable;" >> ${S1}
echo "exit;" >> ${S1}
fi

# this could be extended as needed perhaps
# using a case statement rather than if's

sqlplus xxx/yyy @${S1}

# You don't need to remove the temp file
# The trap takes care of that for you.

[Dave Chamberlin]

Thanks for the replies- the problem was combination of things - I rewrote without any indents and making sure no extra characters etc, and it works. I love unix but shells can be so tempermental on minor things like whitespace, indents, etc. Often I just write a C program to do what is needed - it is usually MUCH easier to deal with!