shell【demo】连接oracle数据库，并执行sql文件

shell连接oracle数据库执行sql语句：

1.sqlplus + 写tnsnames.ora：支持 oracle rac

【jdbc.properties】

jdbc.properties文件部分信息：

jdbc.driver = oracle.jdbc.driver.OracleDriver

jdbc.url = jdbc:oracle:thin:@10.10.10.17:1521:ora12c

jdbc.username = testdb

jdbc.password = test123

jdbc.maxActive= 50

 

【tnsnames.ora】

略

 

 【exec_sql.sh】

#/usr/bin/ksh

#读应用用户的 jdbc.properties 获得数据库连接信息

#执行 update.sql

typeset tomcat_user=\'test\' 

typeset oracle_username=\'oracle\'

typeset shell_type=`cat /etc/passwd|grep ${oracle_username}|grep csh`

 

 

function update_oracle_tab

{

typeset func_name="update_oracle_tab"

typeset tomcat_home=$(echo ~${tomcat_user})

typeset oracle_home=`su - ${oracle_username} -c "env|grep ORACLE_HOME" |grep -w ORACLE_HOME|awk -F \'=\' \'{print $2}\'`

typeset jdbc_file=${tomcat_home}/conf/jdbc.properties

typeset exec_sql="${oracle_home}/exec_sql.sql"

typeset tmp_log="${oracle_home}/tmp_log.log"

typeset tnsnames_file="${oracle_home}/network/admin/tnsnames.ora"

if [ -f ${exec_sql} ];then

  rm -rf ${exec_sql}

fi

if [ -f $tmp_log ];then

  rm -rf $tmp_log

fi

 

#1.在 tnsnames.ora 增加服务

#获得tns信息

user_name=`cat ${jdbc_file}|grep ^jdbc.username|awk -F= \'{print $2}\'`

user_password=`cat ${jdbc_file}|grep ^jdbc.password|awk -F= \'{print $2}\'`

tns_name="ideploy_"${user_name}

IP=`cat ${jdbc_file}|grep ^jdbc.url|awk -F\'@\' \'{print $2}\' |awk -F: \'{print $1}\'`

PORT=`cat ${jdbc_file}|grep ^jbdc.url|awk -F: \'{print $5}\'`

SID=`cat ${jdbc_file}|grep ^jdbc.url|awk -F: \'{print $6}\'`

 

#删除

sed -i "/#BDITNS ${tns_name} Begin/,/#BDITNS ${tns_name} End$/"d ${tnsnames_file}

#增加

if [ -f ${tnsnames_file} ];then

echo " " >> ${tnsnames_file}

echo "#BDITNS ${tns_name} Begin" >> ${tnsnames_file}

echo "${tns_name}=" >> ${tnsnames_file}

echo "(DESCRIPTION = (" >> ${tnsnames_file}

echo " (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ${IP})(PORT = ${PORT})) 

)" >> ${tnsnames_file}

echo "(CONNECT_DATA = (SERVICE_NAME = ${SID}) (SERVER = DEDICATED) ) " >> ${tnsnames_file}

echo ")" >> ${tnsnames_file}

echo "#BDITNS ${tns_name} End" >> ${tnsnames_file}

else

  echo "ERROR" "${func_name}" "[${LINENO}] Config file \"${tnsnames_file}\" doesn\'t exist."

  exit 1

fi

 

#2.执行 update.sql 文件

cp ${tomcat_home}/update.sql ${oracle_home}/update.sql

chmod 777 ${oracle_home}/update.sql

echo "@${oracle_home}/update.sql" >> ${exec_sql}

 

if [ -f ${exec_sql} ];then

  echo "exit sql.sqlcode" >> ${exec_sql}

  chmod 777 ${exec_sql}

  if [ "X${shell_type}" != "X" ];then

    su - ${oracle_username} -c "sqlplus ${user_name}/\\\"${user_password}\\\"@${tns_name} @${exec_sql}" >> ${tmp_log} 2>&1

  else

     su - ${oracle_username} -c "sqlplus \"${user_name}/\\\"${user_password}\\\"@${tns_name}\" @${exec_sql}" >> ${tmp_log} 2>&1

  fi

  cat ${tmp_log}|grep "ORA-"|grep -v "ORA-01918" |grep -v "ORA-01919" >> /dev/null 2>&1

  if [ $? -eq 0];then

    echo "ERROR" "${func_name}" "[${LINENO}] Update table failed ,please check ${tmp_log}"

  else

    rm -rf ${exec_sql}

    rm -rf ${tmp_log}

    echo "INFO" "${func_name}" "[${LINENO}] Update table successfully."

  fi

 

fi

if [ -f ${oracle_home}/update.sql ];then

  rm -rf ${oracle_home}/update.sql

fi

}

 

update_oracle_tab || return $?