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
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