Shell Programming and Scripting

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

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

#!/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 "########################################"