Plsql存储过程执行 – 如何监视

huangapple go评论59阅读模式
英文:

Plsql stored procedure execution - how to monitor

问题

如何监视Toad中存储过程的执行?
我尝试监视的摘要是:

过程ArchiveData(Param1,Param2,Param3,Param4)(

    变量1 := 使用上述参数进行一些计算;
    变量2 := 使用上述参数进行一些计算;

Call_to_another_procedure2(Param3,Param4,变量1);

Call_to_another_procedure3(Param3,Param4,变量1);

结束;

是否有一种方法可以查看执行的所有语句以及包括被调用的过程/函数中的参数值?

我正在使用Toad 14.1
感谢您的帮助!

尝试使用会话浏览器和SQL监视器,但没有帮助。

英文:

How to monitor stored procedure execution in toad ?
Summary of what I'm trying to monitor:

Procedure ArchiveData ( Param 1, Param 2, Param 3, Param 4)(

    Variable 1 := Some calc using above params;
    Variable 2 := Some calc using above params;

Call_to_another_procedure2(Param3, Param 4,variable 1);

Call_to_another_procedure3(Param3, Param 4,variable 1);

End;

Is there away to look at all the statements executed with the param values including the statements in the called procedure/functions ?

I'm using Toad 14.1
Thanks for your help!

Tried using session browser and sql Monitor but that is not helpful

答案1

得分: 3

PL/SQL调试的典型方式是通过dbms_output.put_line发出消息,这在大多数数据库客户端软件产品中都可以看到,尽管有时您需要在设置/选项中启用它,它可能会显示在与正常结果不同的选项卡或屏幕上。这些产品在内部只是循环调用dbms_output.get_line,并将输出显示给您,直到缓冲区中没有更多内容。您可以通过调用put_line来加载PL/SQL中的缓冲区。

DECLARE
  var1 ...
  var2 ...
BEGIN
  dbms_output.put_line('var1 = '||var1||', var2 = '||var2);
  procedurecall(var1,var2);
  dbms_output.put_line('Got here 2');
  --等等...
END;

其实更重要的第二件事是良好的异常处理。您应该在潜在错误点附近处理异常,并始终确保您要么重新引发给客户端,要么报告这些错误(通过dbms_output,日志表等)并提供完整的错误堆栈和调用堆栈。错误堆栈是最重要的,因为它会告诉您引发异常的确切行号。通常,如果将异常引发给客户端,客户端将为您提供完整的错误堆栈,您会得到所需的信息。如果没有,您也可以自己获得它。

示例:

BEGIN
  --执行可能失败的操作
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_backtrace||CHR(10)||
                         dbms_utility.format_error_stack||CHR(10)||
                         dbms_utility.format_call_stack);
    RAISE;
END;

当然,您也可以以其他方式使用异常处理,比如不重新引发给客户端,而是继续执行或返回状态代码而不是异常等等...但这应该给您一个想法。目标是始终知道在哪里出现了问题,出现问题的行号,在PL/SQL程序中的位置以及哪个异常处理程序堆栈将其重新引发给客户端,等等...都是有用的信息。

至于您的01403错误,请查找可能找不到任何行的SELECT INTO语句,并查找可能不存在的集合/数组下标。

英文:

The typical way of debugging PL/SQL is to emit messages via dbms_output.put_line, which you can see in most database client software products, though sometimes you have to enable it in settings/options and it may appear in a separate tab or screen than your results normally do. Internally those products are simply calling dbms_output.get_line in a loop and displaying the output to you until there isn't any more in the buffer. You load the buffer in PL/SQL by calling put_line.

DECLARE
  var1 ...
  var2 ...
BEGIN
  dbms_output.put_line('var1 = '||var1||', var2 = '||var2);
  procedurecall(var1,var2);
  dbms_output.put_line('Got here 2');
  --etc...
END;

The second thing, which is actually more important, is good exception handling. You should handle exceptions close to the points of potential error and always make sure you either re-raise to the client or report those errors (dbms_output, logging table, etc..) with the full error stack and call stack. The error stack is the most important, as it will tell you the exact line number where the exception was thrown. Usually if an exception is thrown to the client your client will give you the whole error stack, and you have what you need. If not, you can obtain it yourself.

Example:

BEGIN
  -- do something that can fail
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_backtrace||CHR(10)||
                         dbms_utility.format_error_stack||CHR(10)||
                         dbms_utility.format_call_stack);
    RAISE;
END;

Of course you can use exception handling in other ways as well, like not reraising back to the client but moving forward or returning a status code instead of an exception, etc.. but this should give you an idea. The goal is to always know where something failed, at what line number, and where in your PL/SQL program and what stack of exception handlers re-raised it back to the client, etc.. all useful info.

As for your 01403 error, look for SELECT INTO statements that might not find any rows, and also look for any collection/array subscripts that might not exist.

huangapple
  • 本文由 发表于 2023年3月31日 04:49:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/75892882.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定