Executing sql statement from .sh file

Shell Programming and Scripting
1

Hi,

How to execute sql statements from the .sh file ??

Means, when we run .sh file then the sql statements within it should be get executed one by one from the sqlplus

With Regards

2

Could you be more explicit?

---------- Post updated at 14:41 ---------- Previous update was at 14:35 ----------

I usually use this in my scripts calling .sql file:

sqlplus -silent $GO <<END
start $HOME/sqlscript
exit;
END
3

Hi,

I want to test oracle connection through shell script.

Will Prompt user for
1) schema user name
2) password
3) tns name

I have redirected the output to check_conn.txt and further also want some sql output to be get redirected for verification.

how to do ?

sqlplus $user_name/$password@$tns;exit; > c:/tmp/check_conn.txt

or can u suggest me some other method to verify the oracle connection using shell script ?

With Regards

4

Here is a simple example on how to execute sqlplus from a script:

#!/bin/ksh

sqlplusDefSets="SET HEAD OFF\nSET FEEDBACK OFF\nSET LINESIZE 600\n"
myQuery="SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:MI:SS') FROM DUAL;"
mySQLPlusLogFile="/<PathToSQLPlus>/<LogFile>.log"

execSQLPlus ()
{
	dbConnString="${1:-NULL}"
	dbQuery="${2:-NULL}"
	
	echo "[execSQLPlus] Starting..."
	
	if [ "${dbConnString}" == "NULL" -o "${dbQuery}" == "NULL" ]
	then
		echo "[execSQLPlus] Either database connection string or query are null arguments. Exiting: [${EXIT_ERR:-1}]."
		exit ${EXIT_ERR:-1}
	fi
	
	sqlplusStartTime=${SECONDS}
	
	echo "${sqlplusDefSets}\n${dbOverwriteSets}\n${dbQuery}\nExit\n" | sqlplus -S -L ${dbConnString} 1>"${mySQLPlusLogFile}" 2>"${mySQLPlusLogFile}"
	sqlplusReturnCode=${?}
	
	sqlplusEndTime=${SECONDS}
	sqlplusDiffTime=`expr ${sqlplusEndTime:-0} - ${sqlplusStartTime:-0}`
	
	echo "[execSQLPlus] Ended SQLPlus. Took: [${sqlplusDiffTime}] second(s)."

	dbErrorCount=0
	egrep -i 'ORA-|SP2-|TNS-|Usage' "${mySQLPlusLogFile}" | sort | uniq | \
	while read dbError
	do
		echo "[execSQLPlus] Database error: [${dbError}]."
		dbErrorCount=`expr ${dbErrorCount} + 1`
	done

	if [ ${sqlplusReturnCode} -ne 0 -o ${dbErrorCount} -ne 0 ]
	then
		echo "[execSQLPlus] Database returned errors. Exiting: [${EXIT_ERR:-1}]."
		exit ${EXIT_ERR:-1}
	fi
	
	echo "[execSQLPlus] Ended."
	# return 0
}

execSQLPlus "<ConnectionString: user/pass@${ORACLE_SID}>" "${myQuery}"

echo "########################################"
echo "# Result"
echo "########################################"
cat "${mySQLPlusLogFile}"
echo "########################################"

I hope it helps.