?
Solved

Unix Bourne Shell / SQLPLUS error

Posted on 2012-04-12
4
Medium Priority
?
2,248 Views
Last Modified: 2012-07-11
I am using the following connect logic in a script which works fine with no errors
   sqlplus /nolog <<EOF >> $LOG 2>&1
   CONN $DATABASE_2
   WHENEVER SQLERROR EXIT sql.sqlcode ROLLBACK;
   set serveroutput on
   set echo on
   set lin 140
   BEGIN     
      DBMS_OUTPUT.put_line ('Replace with TEST procedure');         
   END;
   /          
   exit;
!EOF

Open in new window


However, when I try to put this logic in a do/while loop, I get this error:

Syntax error at line 89 : `<<' is not matched.

So, I followed advice I found via google and changed it to:
sqlplus /nolog <<
EOF >> $LOG 2>&1
CONN $DATABASE_2
WHENEVER SQLERROR EXIT sql.sqlcode ROLLBACK;
   set serveroutput on
   set echo on
   set lin 140
   BEGIN     
      DBMS_OUTPUT.put_line ('Replace with TEST Procedure');         
   END;
   /          
   exit;
!EOF

Open in new window


This gives me the following error: Syntax error at line 91 : `newline or ;' is not expected.

Any connection pointers would be greatly appreciated. I am at a loss as to why it's erroring out within a loop.
Thanks!
0
Comment
Question by:Marilyn1374
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
ozo earned 600 total points
ID: 37840599
the ending EOF should look exactly like the the beginning EOF after the <<
and there should be no other whitespace or other characters on the line
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 600 total points
ID: 37841262
Try to remove the "!" before the second EOF, see below

counter=1
LOG=zxxx.txt
while [ $counter -lt 4 ]
do
   sqlplus /nolog <<EOF >> $LOG 2>&1
   CONN duser/dpwd@ddb
   WHENEVER SQLERROR EXIT sql.sqlcode ROLLBACK;
   set serveroutput on
   set echo on
   set lin 140
   BEGIN
      DBMS_OUTPUT.put_line ('Replace with TEST procedure');
   END;
   /
   exit;
EOF
counter=$(( $counter + 1 ))
done

Open in new window

0
 

Author Comment

by:Marilyn1374
ID: 38176816
Unfortunately neither of these worked, thought they should have. I think it's something in how this client is set up. I modified my code to not loop. Assigning half to each expert
0
 

Author Closing Comment

by:Marilyn1374
ID: 38176823
Unfortunately neither of these worked, thought they should have. I think it's something in how this client is set up. I modified my code to not loop. Assigning half to each expert
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question