Calling SQL*PLUS from UNIX Shell script and print the status message about the query

I want to run a SQL code using shell script and return the message whether the SQL query executed successfully or not. For this I have used unix script given below.

#!/bin/sh          

sqlplus -S hr/hr@xe<<EOF

@emp.sql

EOF

var1=$(cat /cygdrive/d/scripts/output.txt | grep -c 'COUNT')

if [ $var1 -ge 1 ];

then

echo "success"

else

echo "failure"

fi

exit;

and emp.sql(called sql file) as

SET ECHO OFF

SPOOL D:\scripts\output.txt

SET LINESIZE 100

SET PAGESIZE 50

SELECT count(*) FROM employees;

SPOOL OFF;

EXIT 0;

When I execute the script I am getting output as

COUNT(*)
----------
   107
./script1.sh: line 13: syntax error: unexpected end of file.

I don't know where I should put EOF statement exactly. Also I am not getting the status message whether it is success or failure which I want as output. Please help. Thanks in advance

Answers


SPOOL D:\scripts\output.txt Isnt this windows way of referring to a file where as in the shell script you referred to the file as /cygdrive/d/scripts/output.txt. I assume you are using linux shell to execute so I executed your script changing the spool line in sql file. It worked fine.

Edit: Also the \ that you used, for the spooled output.txt path, will cause the sqlplus to terminate. Hence the error line 13: syntax error: unexpected end of file. Perhaps add quotes to the path or use the same file path as you used in shell


Need Your Help

Oracle Glassfish 3.0.1 JMS Standalone Remote Client

glassfish client jms

I'm trying to lookup a JMS ConnectionFactory on a remote oracle glassfish server 3.0.1 from a standalone java (SE) client.

Functional testing tools like Jameleon

c# .net functional-testing

Are there any functional testing tools like Jameleon (in java), available in the .net world?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.