how to find out line number,procedure name in PL/sql in case of an error

Question

I am using a D2k 6i form and getting the error on form from stored database(oracle9i) procedure ORA-00001:Unique constraint(.) violated but i m not able to trace out from which procedure it is coming. can anybody help me regarding this

Answer 1

Posting your exception with your question would give us a better idea of what you are confronted with.

Usually an exception will tell you the package and the line number within the error message. From that, you can query the USER_SOURCE table:

SELECT text 
FROM user_source 
WHERE type = 'PACKAGE BODY' 
  AND name = 'myProcName' 
  AND line = [the line number];

It might be useful to know more about the context within which the error was triggered. For that you can use the `BETWEEN' operator:

SELECT text 
FROM user_source 
WHERE type = 'PACKAGE BODY' 
  AND name = 'myProcName' 
  AND line BETWEEN [the line number - 5] AND [the line number +5];

Answer 2

For posterity, here is the solution the OP found:

ok in D2k forms there is an ON-ERROR trigger where you can use the function DBMS_ERROR_TEXT to get the procedure,package name line number of the statement from where the error is coming