Error while using sqlplus command inside 'if' condition in an unix shell script

Shell Programming and Scripting
1

Hi all,
I am using the below given sqlplus command in my unix script to invoke a stored procedure which returns a value .It works fine.

  RET_CODE=$(/opt/oracle/product/10.2.0.4.CL/bin/sqlplus -S    $USER/$PASSWD@$DB_NAME <<EOF
  EXEC MY_PKG.MY_SP (:COUNT);
  PRINT COUNT;
  commit;
  exit;
  EOF )

If I use the same command inside an "if" condition as below:

if [ "$choice" = "1" ];then

  RET_CODE=$(/opt/oracle/product/10.2.0.4.CL/bin/sqlplus -S $USER/$PASSWD@$DB_NAME <<EOF
  EXEC MY_PKG.MY_SP (:COUNT);
  PRINT COUNT;
  commit;
  exit;
  EOF )
fi;

It gives me an error :
0403-057 error '<' is not matched in the line number where EOF) is given.
Can anyone help me in resolving this problem.

Thanks in advance

2

Try this, if [ "$choice" == "1" ];then

and there should not be ; after fi

3

EOF should also be at the very start of the line

...
EOF
)

(it only became obvious that it was not at the start after adding code tags!)

There is no difference between = and == in this context.

And if choice is a number, you should treat it as such:

if [ "$choice" -eq 1 ];
  ...

The extra ; after fi is harmless enough.

4

Thanks for replying .
Actually the control goes inside the "if" condition. So I think the problem is not with the "if" condition.

Also I tried this giving

...
EOF
)

This was also not working.If the problem is because of EOF then when I executed the below given script "which doesn't have if condition"

 RET_CODE=$(/opt/oracle/product/10.2.0.4.CL/bin/sqlplus -S    $USER/$PASSWD@$DB_NAME <<EOF
  EXEC MY_PKG.MY_SP (:COUNT);
  PRINT COUNT;
  commit;
  exit;
  EOF )

Should not have worked :mad: but it is working

5

The ending EOF can be indented as long as it's TABs and not spaces and you use "<<-EOF". Try this code, it should work for you. I'm using ksh.

if [ "${CHOICE}" -eq 1 ]; then
   RET_CODE=$( sqlplus -s /nolog <<-EOF

                  --
                  -- If you connect to the database this way
                  -- your password is not exposed by a "ps -ef"
                  --
                  connect $DBUSER/$USRPWD@$ORA_SID

                  set pages 0 trimout on trimspool on
                  set feedback off termout off
                  set sqlprompt ''

                  select 10
                    from dual;

                  exit

                EOF)
fi

echo ${RET_CODE}

and the result:

./teof.ksh
10

I hope this helps.
Good luck.

6

Thanks a lot :slight_smile:
I will try this

7

It is working... Thanks to everyone :slight_smile: