Oracle存储过程中的错误处理

Is it possible to know the line no. at which an error occurred inside an oracle or SQL server stored procedure?

有可能知道电话号码吗?在oracle或SQL server存储过程中发生错误?

4 个解决方案

#1

In Oracle you can use DBMS_UTILITY.FORMAT_ERROR_STACK to get the error stack and DBMS_UTILITY.FORMAT_CALL_STACK to get the call stack. Both return a varchar2(2000). A nice example of the usage is here http://psoug.org/reference/exception_handling.html in Dan Morgans library. There is a lot of info available and line numbers are amongst them.

在Oracle中，您可以使用DBMS_UTILITY。FORMAT_ERROR_STACK来获取错误堆栈和DBMS_UTILITY。FORMAT_CALL_STACK来获取调用堆栈。返回一个varchar2(2000)。这个用法的一个很好的例子是在Dan Morgans库中的http://psoug.org/reference/exception_handling.html。有很多信息可用，行号也在其中。

#2

In SQL Server, you can catch all of the attributes of the error.

在SQL Server中，您可以捕获错误的所有属性。

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Better yet, create an error table and a stored procedure to insert these values into the table. Then execute the stored procedure in the catch block.

更好的是，创建一个错误表和一个存储过程，将这些值插入到表中。然后在catch块中执行存储过程。

#3

For SQL Server it will give you the line in the message tab when you run the proc from SQL Server Management Studio

对于SQL Server，当您从SQL Server Management Studio运行proc时，它将在message选项卡中为您提供行

For example, if you have this proc

例如，如果你有这个程序

CREATE PROCEDURE prTest
AS
SELECT 1

SELECT 2

SELECT bla FROM SOMETABLE


SELECT 3

GO

and you run it like this

像这样运行。

EXEC prTest

you get this error message

您将得到这个错误消息

Msg 208, Level 16, State 1, Procedure
prTest, Line 7 Invalid object name 'SOMETABLE'.

Msg 208, 16级，状态1，过程prTest，第7行无效对象名“SOMETABLE”。

#4

In your procedure you'll need to catch the exception. You can even send the exception to an error log table. How fun is that! Or, you could just DBMS_OUTPUT the message, but it might be pretty long. DBMS_OUTPUT has a limit on message sizes. Default is 20000 characters.

在您的过程中，您需要捕获异常。您甚至可以将异常发送到错误日志表。是多么有趣!或者，您可以只输出DBMS_OUTPUT消息，但它可能很长。DBMS_OUTPUT对消息大小有限制。默认是20000个字符。

You can even create custom exceptions.

您甚至可以创建自定义异常。

You'll first need a variable

首先需要一个变量

EM VARCHAR(2000);

Then this at the end of your procedure.

然后在程序的最后。

      EXCEPTION WHEN OTHERS THEN
      EM := substr(SQLERRM, 1, 2000) ;
      ROLLBACK;
      INSERT INTO ERROR_LOG(ERROR_TIME, PROC_NAME , ERROR_MSG)
      VALUES(SYSTIMESTAMP , 'PKG.get_stuff', EM);
      COMMIT;
      RETURN NULL;

#1

#2