topic Re: if then else script wont work with oracle command in Operating System - HP-UX

if then else script wont work with oracle command

Dave Chamberlin — Mon, 18 Apr 2005 17:39:12 GMT

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?

Re: if then else script wont work with oracle command

Biswajit Tripathy — Mon, 18 Apr 2005 17:55:06 GMT

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

Re: if then else script wont work with oracle command

Julio Yamawaki — Mon, 18 Apr 2005 18:55:14 GMT

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,

Re: if then else script wont work with oracle command

Peter Godron — Tue, 19 Apr 2005 02:32:02 GMT

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

Re: if then else script wont work with oracle command

renarios — Tue, 19 Apr 2005 02:55:50 GMT

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

Re: if then else script wont work with oracle command

Etienne Roseau — Tue, 19 Apr 2005 05:37:59 GMT

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.

Re: if then else script wont work with oracle command

Steve McKenna_3 — Tue, 19 Apr 2005 08:51:25 GMT

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.

Re: if then else script wont work with oracle command

Steve McKenna_3 — Tue, 19 Apr 2005 08:54:01 GMT

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

Re: if then else script wont work with oracle command

A. Clay Stephenson — Tue, 19 Apr 2005 09:13:49 GMT

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.

Re: if then else script wont work with oracle command

Dave Chamberlin — Tue, 19 Apr 2005 09:55:58 GMT

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!