Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to run SQL in shell script

How do you run a SQL command in a shell script while setting a variable? I had tried this method and it isn't executing the command, it is thinking the command is just a string.

#!/bin/ksh
variable1=`sqlplus -s username/pw@oracle_instance <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select count(*) from table;
EXIT;
EOF`
if [ -z "$variable1" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $variable1
fi
like image 270
Gabe M Avatar asked Oct 04 '13 13:10

Gabe M


2 Answers

#!/bin/ksh
variable1=$( 
echo "set feed off
set pages 0
select count(*) from table;
exit
"  | sqlplus -s username/password@oracle_instance
)
echo "found count = $variable1"
like image 105
Gabe M Avatar answered Oct 23 '22 16:10

Gabe M


You can use a heredoc. e.g. from a prompt:

$ sqlplus -s username/password@oracle_instance <<EOF
set feed off
set pages 0
select count(*) from table;
exit
EOF

so sqlplus will consume everything up to the EOF marker as stdin.

like image 45
Brian Agnew Avatar answered Oct 23 '22 15:10

Brian Agnew