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 t开发者_StackOverflow中文版race out from which procedure it is coming. can anybody help me regarding this
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
I've come across this pattern after much research, head banging and gnashing of teeth:
CREATE OR REPLACE PACKAGE BODY my_schema.package_name
IS
PROCEDURE foo
IS
BEGIN
-- Call stored procedures/functions that throw unhandled exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR! - '
|| DBMS_UTILITY.FORMAT_ERROR_STACK
|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END foo;
END;
The DBMS_UTILITY.FORMAT_ERROR_STACK
function seems to give the error code and message, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
seems to give an honest to goodness stack trace, complete with line numbers and stored procedure names in Oracle 10g at least.
I'm not sure if those functions are available in Oracle 9i. I couldn't find much information about this sort of thing even for Oracle 10g, so I thought I would at least post this answer since 9i is quite old (and so it 10g for that matter).
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];
精彩评论