bash 在 shell 脚本中连接到 sqlplus 并运行 SQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10277983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect to sqlplus in a shell script and run SQL scripts
提问by Farshid
I have a .sql file, which is a bunch of oracle pl/sql commands and I want to create a shell script to run these commands.
我有一个 .sql 文件，它是一堆 oracle pl/sql 命令，我想创建一个 shell 脚本来运行这些命令。
Suppose that user/pass@serveris my credentials. What will be the shell script to do such a task?
假设这user/pass@server是我的凭据。执行此类任务的 shell 脚本是什么？
回答by NetBear
For example:
例如：
sqlplus -s admin/password << EOF
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
@pl_script_1.sql
@pl_script_2.sql
exit;
EOF
回答by Blaine DeLancey
Wouldn't something akin to this be better, security-wise?:
在安全方面，类似的东西不是更好吗？：
sqlplus -s /nolog << EOF
CONNECT admin/password;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
@pl_script_1.sql
@pl_script_2.sql
exit;
EOF
回答by darwinbaisa
If you want to redirect the output to a log file to look for errors or something. You can do something like this.
如果要将输出重定向到日志文件以查找错误或其他内容。你可以做这样的事情。
sqlplus -s <<EOF>> LOG_FILE_NAME user/passwd@host/db
#Your SQL code
EOF
回答by Rafa? Sardaw
This should handle issue:
这应该处理问题：
- WHENEVER SQLERROR EXIT SQL.SQLCODE
- SPOOL ${SPOOL_FILE}
- $RC returns oracle's exit code
- cat from $SPOOL_FILE explains error
- 每当 SQLERROR 退出 SQL.SQLCODE
- 线轴 ${SPOOL_FILE}
- $RC 返回 oracle 的退出代码
- $SPOOL_FILE 中的 cat 解释了错误
SPOOL_FILE=${LOG_DIR}/${LOG_FILE_NAME}.spool
SQLPLUS_OUTPUT=`sqlplus -s "$SFDC_WE_CORE" <<EOF
SET HEAD OFF
SET AUTOPRINT OFF
SET TERMOUT OFF
SET SERVEROUTPUT ON
SPOOL ${SPOOL_FILE}
WHENEVER SQLERROR EXIT SQL.SQLCODE
DECLARE
BEGIN
foooo
--rollback;
END;
/
EOF`
RC=$?
if [[ $RC != 0 ]] ; then
echo " RDBMS exit code : $RC " | tee -a ${LOG_FILE}
cat ${SPOOL_FILE} | tee -a ${LOG_FILE}
cat ${LOG_FILE} | mail -s "Script ${INIT_EXE} failed on $SFDC_ENV" $SUPPORT_LIST
exit 3
fi
回答by Ketzak
Some of the other answers here inspired me to write a script for automating the mixed sequential execution of SQL tasks using SQLPLUS along with shell commands for a project, a process that was previously manually done. Maybe this (highly sanitized) example will be useful to someone else:
此处的其他一些答案启发我编写脚本，使用 SQLPLUS 以及项目的 shell 命令自动执行 SQL 任务的混合顺序执行，该过程以前是手动完成的。也许这个（高度消毒的）示例对其他人有用：
#!/bin/bash
acreds="user_a/supergreatpassword"
bcreds="user_b/anothergreatpassword"
hoststring='fancyoraclehoststring'
runsql () {
# param 1 is
sqlplus -S /nolog << EOF
CONNECT @$hoststring;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
exit;
EOF
}
echo "TS::$(date): Starting SCHEM_A.PROC_YOU_NEED()..."
runsql "$acreds" "execute SCHEM_A.PROC_YOU_NEED();"
echo "TS::$(date): Starting superusefuljob..."
/var/scripts/superusefuljob.sh
echo "TS::$(date): Starting SCHEM_B.SECRET_B_PROC()..."
runsql "$bcreds" "execute SCHEM_B.SECRET_B_PROC();"
echo "TS::$(date): DONE"
runsqlallows you to pass a credential string as the first argument, and any SQL you need as the second argument. The variables containing the credentials are included for illustration, but for security I actually source them from another file. If you wanted to handle multiple database connections, you could easily modify the function to accept the hoststring as an additional parameter.
runsql允许您将凭据字符串作为第一个参数传递，并将您需要的任何 SQL 作为第二个参数传递。包含凭据的变量用于说明，但为了安全起见，我实际上从另一个文件中获取它们。如果您想处理多个数据库连接，您可以轻松修改该函数以接受主机字符串作为附加参数。
