Unix Bourne Shell / SQLPLUS error

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!
Marilyn1374Asked:
Who is Participating?
 
ozoConnect With a Mentor Commented:
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
 
SujithConnect With a Mentor Data ArchitectCommented:
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
 
Marilyn1374Author Commented:
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
 
Marilyn1374Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.