今天,在工作中,我们面临以下问题:在其中一个shell中,我们有一个类似这样的部分:
SQL*Plus user/pass@DB EOF
copy command 1 from one DB to another different DB
copy command 2 from one DB to another different DB
copy command 3 from one DB to another different DB
EOF
//check if the command worked
IF $? -eq 0 then
...
else
EXIT the shell
end if
但是在第2行“ 将命令2从一个DB复制到另一个DB ”中,我们遇到了一个问题。表形式DB1的结构(假设)有2列,在DB2中不是。我的问题是如何捕获sql * plus错误(CPY-xxxx)?这样以后我就可以防止在检查COPY是否起作用后外壳进一步发展。
此刻我的计划是:
在列数之前检查它们是否不同,然后我知道这将是一个问题并退出外壳。
我知道这类错误不是ORACLE类型的,因此无法像使用WHENEVER sqlerror这样捕获它们:
SQL*Plus user/pass@DB EOF
whenever sqlerror exit 2
copy command 1 from one DB to another different DB
copy command 2 from one DB to another different DB
copy command 3 from one DB to another different DB
exit 3;
EOF
//check if the command worked
IF $? -eq 3 then
...
else
EXIT the shell
end if
,都不是这样的:
SQL*Plus user/pass@DB EOF
BEGIN
execute immediate 'copy command 2 from one DB to another different DB';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION ...
EOF
//check if the command worked
IF $? -eq 0 then
...
else
EXIT the shell
end if
更新
解决方案(不好)!
SQL*Plus user/pass@DB EOF
whenever sqlerror exit 2
copy command 1 from one DB to another different DB
copy command 2 from one DB to another different DB
copy command 3 from one DB to another different DB
exit 3;
EOF >> SHELL_VARIABLE
//Check in the shell if the SHELL_VARIABLE (that will be a text I redirected the output from SQL*Plus) containts code like CRQ-XXXX then clearly we have an errore and exit