PLSQL Exceptions

[llmclaughlin]

Is there a way to get the line number where the error occured?

Louie

 

[SantaMufasa]

Louie,

Can you give us a few more details?...What environment are you running in (e.g., SQL*Plus, TOAD, SQL*Developer, et cetera)? What class of error are we taking about? For example, [ul][li]Syntax error[/li][li]Parse error[/li][li]Run-time error[/li][li]Logic error[/li][/ul]Can you/May you post a copy of the error you are seeing? If running from SQL*Plus, what do you see when you enter the command, "Show errors".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[llmclaughlin]

I just want to know with the code below is there a way to to get the line number.
I do use Toad while debugging, if there is a way to get just for that, then that will do.

EXCEPTION
WHEN OTHERS
THEN
err_msg := substr(SQLERRM, 1, 200);
fileHandler := UTL_FILE.FOPEN ('c:\temp', 'sqlerr.txt', 'a');
UTL_FILE.PUT_LINE (fileHandler, SYSDATE);
UTL_FILE.PUT_LINE (fileHandler, 'ParseReceivedFiles');
UTL_FILE.PUT_LINE(filehandler, 'File Name: ' || inFILENAME);
-- UTL_FILE.PUT_LINE(filehandler, 'Line number :' || $$plsql_line);
UTL_FILE.PUT_LINE (fileHandler, err_msg);
UTL_FILE.FCLOSE (fileHandler);
om_interface.send_email ('Error in procedure ParseReceivedFiles ',
err_msg || '. --- File Name:' || inFILENAME,
'LIMSAdmin@transnetyx.com');

Louie

 

[SantaMufasa]

Louie,

TOAD does give the line numbers of your code. Let me know if I'm missing something.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

Everything I've ever read about pl/sql indicates that great care must be taken in an exception handler to avoid losing valuable context information about where in your code the exception being raised first occurred. The following is a link to one method of dealing with the issue. It requires that you be at least on Oracle 10g, in order to be able to call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

http://apex.oracle.com/pls/otn/f?p=2853:4:964343748348449::NO::P4_QA_ID:181