Solved

Unix Bourne Shell / SQLPLUS error

Posted on 2012-04-12
4
2,088 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
ozo earned 150 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 150 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

737 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