Error handling in Oracle stored procedure

Is it possible to know the line no. at which an error开发者_开发技巧 occurred inside an oracle or SQL server stored procedure?

---

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.

---

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

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.

---

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

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'.

---

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.

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;