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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Error while using sqlplus command inside 'if' condition in an unix shell script
# 1  
Old 10-30-2010
Error while using sqlplus command inside 'if' condition in an unix shell script

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.
Code:
  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:
Code:
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

Last edited by Scott; 10-30-2010 at 08:31 AM.. Reason: Please use code tags
# 2  
Old 10-30-2010
Try this, if [ "$choice" == "1" ];then

and there should not be ; after fi
# 3  
Old 10-30-2010
EOF should also be at the very start of the line

Code:
...
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:

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

The extra ; after fi is harmless enough.

Last edited by Scott; 10-30-2010 at 08:44 AM..
# 4  
Old 10-30-2010
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
Code:
...
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"
Code:
 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 Smilie but it is working

Last edited by Scott; 10-30-2010 at 10:17 AM.. Reason: Use code tags, please...
# 5  
Old 10-30-2010
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.

Code:
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:
Code:
./teof.ksh
10

I hope this helps.
Good luck.
# 6  
Old 10-30-2010
Thanks a lot Smilie
I will try this

Last edited by Scott; 10-30-2010 at 10:31 AM.. Reason: Code tags, please...
# 7  
Old 11-01-2010
It is working... Thanks to everyone Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need Multiple checks inside if condition in a bash shell script

Hi, I need to perform the untar and rm operation if the file found is a .tar and does not have test.tar or hello.tar as the file names. Below is the loop to check the same. for tf in *.tar do if ] then found=1 ... (1 Reply)
Discussion started by: mohtashims
1 Replies

2. Shell Programming and Scripting

Control not returning from Sqlplus to calling UNIX shell script.

Hello All, I have exactly same issue @vikas_trl had in following link: https://www.unix.com/shell-programming-and-scripting/259854-control-not-returning-sqlplus-calling-unix-shell-script.html I wonder if he or somebody else could find the issue's cause or the solution. Any help would... (4 Replies)
Discussion started by: RicardoQ
4 Replies

3. Shell Programming and Scripting

Sqlplus inside shell script writing to tmp file

Hi, facing an issue while calling sqlplus inside shell script. It for some reason goes to tmp file to write something and i get error as permission denied as i dont have access there. ANy idea why sqlplus writes in /tmp and how to change or stop this ? (2 Replies)
Discussion started by: rushikeshs
2 Replies

4. Shell Programming and Scripting

If else condition inside for loop of awk command in UNIX shell scripting

Hi , Please excuse me for opening a new thread i am unable to find out the syntax error in my if else condition inside for loop in awk command , my actual aim is to print formatted html td tag when if condition (True) having string as "failed", could anyone please advise what is the right... (2 Replies)
Discussion started by: karthikram
2 Replies

5. Solaris

Error during running sqlplus command from shell script in Solaris

I am using following code to connect to oracle database from solaris shell script. which will try thrice to connect the database ...at the 4rth atempt it will exir=t. count=0 while ; do sqlplus -s $usrname/$password@dbSID <<-EOF | tee $logfile WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR... (4 Replies)
Discussion started by: millan
4 Replies

6. Shell Programming and Scripting

how to use split command in unix shell with a condition

Hi all, I have a file which I want to split into several files based on a condition. This files has several records. I want one record per file. Each record ends with a //. So, I want to separate files based on this condition. I want split files to be named with the name across the field ID (for... (2 Replies)
Discussion started by: kaav06
2 Replies

7. Shell Programming and Scripting

how to use sqlplus command inside for loop

I tried this: for region in 'raj' 'kt' 'kol' 'krl' 'chn' 'dl' 'hr' 'bih' 'ap' do sqlplus -s huw$region/`echo $region`huw#321@huw$region<<! set serveroutput on begin select count(*) from tcd_preferred_cust_201109 end; / exit; done but the error shows like: Syntax error at line 4 :... (1 Reply)
Discussion started by: deepakprasad29@
1 Replies

8. UNIX for Dummies Questions & Answers

calling a unix shell script from sqlplus

I want to execute a shell script from sqlplus prompt and get its output back to sqlplus. Is this possible? if yes just give me an example for doing that. (2 Replies)
Discussion started by: boopathyvasagam
2 Replies

9. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

10. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies
Login or Register to Ask a Question