如何通过shell脚本捕获存储过程的结果? [英] How to capture the result of stored procedure through shell script?

问题描述

我正在尝试通过shell脚本执行存储过程，并尝试从存储过程中获取收益，但是另一方面，我没有从存储过程中得到任何东西，而与sqlplus提示符相同，我能够得到结果

I'm trying to execute stored procedure through shell script and try to get return from stored procedure but I didn't get any thing from the stored procedure on other hand same thing I do with sqlplus prompt and I'm able to get the result

sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare

DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);

end;

EOF

通过sqlplus提示符

Through sqlplus prompt

SQL> set serveroutput on
declare

DE_REC_COUNT number;
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);

end;  

0

PL/SQL procedure successfully completed.

推荐答案

由于您没有

The version of the anonymous block in the shell script will not be executed as shown, because you don't have a slash after the block to run it. If you run that you get no output at all. If you change it to have a slash:

sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare
  DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/
EOF

然后您将看到:

0

PL/SQL procedure successfully completed.

您已经在SQL * Plus中显示了交互式版本，但也没有使用斜杠，但是必须具有该版本才能查看显示的输出.

You've shown the interactive version in SQL*Plus without the slash too, but you must have had that to see the output you showed.

如果您想要零(它似乎来自过程中的dbms_output调用，而不是直接来自您的匿名块)–您稍后可以引用的shell变量na，则可以将heredoc的输出分配给变量:

If you want the zero - which seems to be coming from a dbms_output call in your procedure, rather than directly from your anonymous block - n a shell variable you can refer to later, you can assign the output of the heredoc to a variable:

MY_VAR=`sqlplus -silent xxx@xxx <<EOF
set serveroutput on
set feedback off
declare

DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/

EOF`

printf "Got back MY_VAR as %s\n" ${MY_VAR}

请注意，我已经添加了set feedback off，因此您看不到PL/SQL procedure successfully completed行.现在，当您运行该程序时，您将看到:

Note that I've added set feedback off so you don't see the PL/SQL procedure successfully completed line. Now when you run that you'll see:

Got back MY_VAR as 0

，您可以使用${MY_VAR}做任何您需要做的事情.不过，这取决于您所说的捕获".

and you can do whatever you need to with ${MY_VAR}. It depends what you mean by 'capture' though.

这篇关于如何通过shell脚本捕获存储过程的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！