oracle sqlplus 命令行问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/899318/
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
oracle sqlplus command line question
提问by ipr
Is there a way to detect missing command line parameters from the oracle script
有没有办法从 oracle 脚本中检测丢失的命令行参数
for example my oracle script test.sql expects 3 parameters to run. my script is executed from the install program like this sqlplus /NOLOG @test.sql userid pw tablespace When one of the parameters is missing, the sqlplus prompting for the userinput. This cannot be recognized from install program. I need to find the missing param inside the script and exit with error code. Is it feasible ?
例如我的 oracle 脚本 test.sql 需要运行 3 个参数。我的脚本是从安装程序执行这样的 sqlplus /NOLOG @test.sql userid pw tablespace 当缺少其中一个参数时，sqlplus 提示用户输入。这无法从安装程序中识别出来。我需要在脚本中找到丢失的参数并以错误代码退出。可行吗？
回答by DCookie
I don't think there is a way to do this directly with sqlplus. You can wrap the sqlplus execution in a command/shell script that checks the parameters, then calls sqlplus with the validated parameter list.
我认为没有办法直接用 sqlplus 做到这一点。您可以将 sqlplus 执行包装在检查参数的命令/shell 脚本中，然后使用经过验证的参数列表调用 sqlplus。
回答by Plasmer
Wrapping the call to sqlplus in a shell script seems like the easiest way to do this but could you also call the script using:
在 shell 脚本中包装对 sqlplus 的调用似乎是最简单的方法，但您是否也可以使用以下方法调用脚本：
sqlplus /NOLOG @test.sql <userid> <pw> <tablespace> empty empty empty
and check inside the script if any of the values are equal to "empty"? That way the script won't hang even if no parameters are passed because you'll still have the three filler parameters.
并检查脚本内部是否有任何值等于“空”？这样，即使没有传递任何参数，脚本也不会挂起，因为您仍然拥有三个填充参数。
You could check using something like this: (using && instead of & means you will only be prompted for the value once)
您可以使用以下内容进行检查：（使用 && 而不是 & 意味着您只会被提示输入一次该值）
var status number;
set verify on
whenever sqlerror exit 1;
begin
:status := 0;
select decode('&&1','empty',1,:status) into :status from dual;
select decode('&&2','empty',2,:status) into :status from dual;
select decode('&&3','empty',3,:status) into :status from dual;
IF :status != 0 THEN
RAISE_APPLICATION_ERROR (
-20000+-1*:status,'parameter '|| :status || ' is missing');
END IF;
end;
/
The return value when the inputs are invalid will be 1. Without knowing what valid values are for the parameters, it's hard to know which one was omitted.
输入无效时的返回值将是 1。在不知道参数的有效值是什么的情况下，很难知道省略了哪个。
