How to call Oracle SQL query using bash shell script-CodePudding

How to call a sql query using bash shell script. I tried the below but seems there is some syntax error:

#!/bin/sh
LogDir='/albt/dev/test1/test2/logs' # log file 
USER='test'     #Enter Oracle DB User name
PASSWORD='test'  #Enter Oracle DB Password
SID='test'        #Enter SID
sqlplus -s << EOF > ${LogDir}/sql.log 
${DB_USER_NAME}/${DB_PASSWORD}@${DB_SID} 
SELECT count(1) FROM dual; #  SQL script here to get executed  
EOF 
var=$(SELECT count(1) FROM dual)

I'm getting - unexpected token error

#!/bin/sh
user="test"
pass="test"
var="$1"
sqlplus -S $user/$pass <<EOF
 SELECT * FROM tableName WHERE username=$var;  
 exit;
EOF

I'm getting - sqlplus: command not found -- when I run the above script

Can anyone guide me?

CodePudding user response：

In your first script, one error is in the use of count(1). The whole line is

var=$(SELECT count(1) FROM dual)

This means that the shell is supposed to execute a program named SELECT with the parameters count(1), FROM and dual, and stores its stdout into the variable var. I think you want instead to feed a SELECT command into sqlplus, i.e. something like

var=$(sqlplus .... )

In your second script, the error message simply means that sqlplus can not be found in your path. Either add it to the PATH or provide the absolute path to the command explicitly.