Catching AND ID'ing Custom Oracle Exception

[MadJock]

Hi,

I have an Oracle stored procedure that will raise an application error under certain conditions. Ther actual errors raised may vary.

Whichever error I raise comes back as an OracleException. I have not found any 'number' property within the oracle exception to identify exactly which error was thrown by my stored procedure.

In the code below, I have included 3 examples - the first one I don't want to have to do, the second one seems more achievable and the third would be the ideal but I've no idea how to do.

// Example 1 - last resort

try{
	db.ExecuteNonQuery(cmd);
} catch ( OracleException ex){
	
	if (ex.Message == "MyCustomError1"){
		// Do some stuff
	} elseif (ex.Message == "MyCustomError2"){
		// Do somethinf else
	}

	//	 and so on ...
}

// Example 2 - possible solution
// Not sure how to retrieve error number though

try{
	db.ExecuteNonQuery(cmd);
} catch ( OracleException ex){
	
	if (ex.Number == 1){
		// Do some stuff
	} elseif (ex.Number == 2){
		// Do somethinf else
	}

	//	 and so on ...
}

// Example 3 - would really like to have this, but
//  not sure how to map the custom oracle exception to 
//  my exception class
public class CustomException1 : Exception{
	// Implementation ommitted
}

// Usage
try{
	db.ExecuteNonQuery(cmd);
} catch ( CustomException1 ex){
	// do something
} catch (CustomException2 ex){
	// do something else
}

Would be very gratefuul for any hints or tips.

Thanks,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[cappmgr]

This is a custom exception handler and dal. I just use it to test stubs and put the error out to an html page. I would say it is close to your third idea. You will want to create your custom exceptions by inheriting ApplicationException.
custom exception handler

using System; 
using System.IO; 
using System.Data.OracleClient; 
namespace CSTestWebForms
{ 
	public class TSPException : ApplicationException 
	{ 

		public TSPException() 
		{ 
		} 

		public TSPException(Exception e) 
		{ 
			LogExceptions(e); 
			System.Web.HttpContext.Current.Response.Redirect("~/Exceptions.html"); 
		} 

		public TSPException(OracleException OraEx) 
		{ 
			StringWriter swLogMsg = new StringWriter(); 
			swLogMsg.WriteLine("Date and Time of OracleException " + DateTime.Now.ToString() + "<br>"); 
			swLogMsg.WriteLine("Exception Code : " + OraEx.Code.ToString() + "<br>"); 
			swLogMsg.WriteLine("Message : " + OraEx.Message.ToString() + "<br>"); 
			swLogMsg.WriteLine("Source : " + OraEx.Source.ToString() + "<br>"); 
			swLogMsg.WriteLine("StackTrace : " + OraEx.StackTrace.ToString() + "<br>"); 
			WriteToWebLogFile(swLogMsg); 
			System.Web.HttpContext.Current.Response.Redirect("~/Exceptions.html"); 
		} 

		public TSPException(OracleException OraEx, string strParms) 
		{ 
			StringWriter swLogMsg = new StringWriter(); 
			swLogMsg.WriteLine("Date and Time of OracleException " + DateTime.Now.ToString() + "<br>"); 
			swLogMsg.WriteLine("Exception Code : " + OraEx.Code.ToString() + "<br>"); 
			swLogMsg.WriteLine("Message : " + OraEx.Message.ToString() + "<br>"); 
			swLogMsg.WriteLine("Source : " + OraEx.Source.ToString() + "<br>"); 
			swLogMsg.WriteLine("StackTrace : " + OraEx.StackTrace.ToString() + "<br>"); 
			swLogMsg.WriteLine(strParms); 
			WriteToWebLogFile(swLogMsg); 
			System.Web.HttpContext.Current.Response.Redirect("~/Exceptions.html"); 
		} 

		public void LogExceptions(Exception ex) 
		{ 
			StringWriter swLogMsg = new StringWriter(); 
			swLogMsg.WriteLine("Date and Time of Exception " + DateTime.Now.ToString() + "<br>"); 
			swLogMsg.WriteLine("Message: " + ex.Message.ToString() + "<br>"); 
			swLogMsg.WriteLine("Source: " + ex.Source.ToString() + "<br>"); 
			swLogMsg.WriteLine("StackTrace: " + ex.StackTrace.ToString() + "<br>"); 
			swLogMsg.WriteLine("InnerException StackTrace: " + ex.InnerException.StackTrace.ToString() + "<br>"); 
			WriteToWebLogFile(swLogMsg); 
		} 

		public void WriteToWebLogFile(StringWriter sw) 
		{ 
			StreamWriter stw = new StreamWriter(System.Web.HttpContext.Current.Server.MapPath("Exceptions.html"), false); 
			try 
			{ 
				try 
				{ 
					stw.WriteLine("<html><body>"); 
					stw.WriteLine(sw.ToString()); 
					stw.WriteLine("</body></html>"); 
				} 
				catch (Exception Ex) 
				{ 
					System.Web.HttpContext.Current.Response.Write(Ex.ToString()); 
				} 
			} 
			finally 
			{ 
				stw.Close(); 
			} 
		} 
	} 
}

dal

using System; 
using System.Text; 
using System.Data; 
using System.Data.OracleClient; 
namespace CSTestWebForms
{ 
	class DAL 
	{ 
		public static DataSet GetRefRateCap() 
		{ 
			string myConnString = "User ID=gwbush;Password=bonehead;Data Source=texas;"; 
			OracleConnection myConnection = new OracleConnection(myConnString); 
			OracleCommand myCommand = new OracleCommand("PKG.GetRateCaps", myConnection); 
			myCommand.CommandType = CommandType.StoredProcedure; 
			myCommand.Parameters.Add(new OracleParameter("c_o_RateCaps", OracleType.Cursor)).Direction = ParameterDirection.Output; 
			DataSet ds = new DataSet(); 
			OracleDataAdapter MyDA = new OracleDataAdapter(myCommand); 
			try 
			{ 
				myConnection.Open(); 
				MyDA.Fill(ds); 
				return ds; 
			} 
			catch (OracleException OracleEx) 
			{ 
				throw new TSPException(OracleEx, WriteOutParms(myCommand)); 
			} 
			catch (Exception e) 
			{ 
				throw new TSPException(e); 
			} 
			finally 
			{ 
				myConnection.Close(); 
			} 
		} 

		public static string WriteOutParms(OracleCommand myCommand) 
		{ 
			StringBuilder sb = new StringBuilder(); 
			sb.Append("Parameters below: "); 
			sb.Append("<br>"); 
			foreach (OracleParameter p in myCommand.Parameters) 
			{ 
				sb.Append(p.ParameterName.ToString()); 
				sb.Append(" = "); 
				if (p.Value != null) 
				{ 
					sb.Append(p.Value.ToString()); 
					sb.Append("<br>"); 
				} 
			} 
			return sb.ToString(); 
		}
	} 
}

Hope this helps,
Marty
Marty