extracting message from an ORA error

My sp in Oracle 9i returns the following error string to the front end:

example 1:- ORA-20002: Login ID Already Exists ORA-06512: at
"CARE.SP_USER2", line 286 ORA-06512: at line 1 

example 2:- ORA-20002: The following are not been approved or not entered for
the selected financial year and dealer Bench Mark Master, Business Parameters,
Profit & Loss, Balance Sheet ORA-06512: at "CARE.SP_RISK_SCORE",
line 585 ORA-06512: at line 1 

 I have to extract the message underlined above which varies in length for
different errors and show it to the user.

substring wont do cos it takes index which is not fixed in the above case.

Any help

Why don't u take a VARCHAR column and set its size to something large like 2000.
Put the error message in this variable and return it from ur SP. Let me know if
you meant something else in your post.

--------------------------------------------------------------------------------
--------

Please click Mark As Answer if this helped in solving your problem.

this wont help cos I am using raise_application_error at my SP which directly
transfers control to my Application,

But I solved the problem in the front end by making a method:-publicstaticstring
ThrowException(Exception ex)

{

 string error = ex.Message;

int upperIndex;if (error.Contains("ORA"))

{

error = error.Remove(0, 11); //for the first ORA-20002: on left hand
sideupperIndex = error.IndexOf("ORA");

error = error.Substring(0, upperIndex);return error;

}

elsereturn error;

}

 

Thanx a lot for replying