Home » SQL & PL/SQL » SQL & PL/SQL » GetLine Number of Error in PL/SQL Proc
GetLine Number of Error in PL/SQL Proc [message #202430] Thu, 09 November 2006 14:20 Go to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
Hi, I have a stored proc that runs several other procs & functions. I want to put an exception block in this proc to handle errors I haven't coded for in the other procs & functions. None of the other procs or functions have an exception block. The errors are propagating to the calling proc, but I am not getting the line numbers of the errors.

The code below puts this entry in the tblUpdateLog:
Error in P_DNAUPdateStartWrapper -1422 ORA-01422: exact fetch returns more than requested number of rows

What I want is this:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "XYZ.F_ADDEMPLOYEE", line 27
ORA-06512: at "XYZ.P_UPDATEEMPINFOFROMDATAFEED", line 397
ORA-06512: at "XYZ.P_UPDATESTARTWRAPPER", line 16


Is there a way to get the line numbers of the errors?


CREATE OR REPLACE PROCEDURE P_UpdateStartWrapper IS
ErrNum number;
ErrDesc Char(100);
sTo CONSTANT varChar2(21) := 'xyz@mayberry.com
sFrom CONSTANT varChar2(19) := 'ORAJOB@mayberry.com
sSubject CONSTANT varChar2(18) := 'ERROR NOTIFICATION';
sBody CONSTANT varchar(71) := 'Error in P_UpdateStartWrapper execution.  See tblUpdateLog for entry';

BEGIN
	 --call procs to update lookup tables 
	 P_UpdateA;
	 P_UpdateB;
	 P_UpdateC;
	 --now call proc to update the emp data
	 P_UpdateEmpInfoFromDataFeed;
	 EXCEPTION
	 
	     WHEN OTHERS THEN
		  ErrNum := SQLCODE;
		  ErrDesc := substr(SQLERRM,1, 100);
		  Insert into tblUpdateLog(dtmUpdateDate, mmoComments) 
		  VALUES(sysdate, 'Error in P_UpdateStartWrapper ' || ErrNum || ' ' || ErrDesc);
			
		  SendEmail(sTo, sSubject, sBody);
END P_UpdateStartWrapper;
/

Re: GetLine Number of Error in PL/SQL Proc [message #202437 is a reply to message #202430] Thu, 09 November 2006 15:24 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Whoops, I read it too fast. My answer doesn't answer your question.

But to add, I'd say that you should put exception blocks in all your procedures, or this type of annoying thing can and will happen.

[Updated on: Thu, 09 November 2006 15:27]

Report message to a moderator

Re: GetLine Number of Error in PL/SQL Proc [message #202473 is a reply to message #202430] Fri, 10 November 2006 00:05 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

just type line number on sql prompt you will find your statement of that line

example
SQL> select
2 *
3 from
4 yarn_coding
5 ;

YARN_ID YARN_ QUALITY_ID BRAND_ID
--------- ----- ---------- ---------
1 16/1 1 1
2 16/1 2 1
3 20/2 1 1
4 20/2 4 1
5 20/2 2 1
6 30/1 2
7 40/2 1

7 rows selected.

SQL> 2
2* *
SQL> 3
3* from
SQL> 4
4* yarn_coding

i hope it is useful to you

regards

Muhammad Shahid Mughal
Faisalabad Pakistan
Re: GetLine Number of Error in PL/SQL Proc [message #202482 is a reply to message #202473] Fri, 10 November 2006 00:27 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
But OP has asked how to find line number..how this would be useful?
Re: GetLine Number of Error in PL/SQL Proc [message #202488 is a reply to message #202430] Fri, 10 November 2006 00:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take a look here
Previous Topic: Global temp table Vs Nested table.
Next Topic: Shadow and Stagging Tables
Goto Forum:
  


Current Time: Fri Dec 09 00:03:19 CST 2016

Total time taken to generate the page: 0.14522 seconds