Enterprise Library Data Access w\Oracle Number Parameter

Topics: Data Access Application Block
Apr 19, 2007 at 11:23 PM
I am starting to use the Enterprise Library to access the packages and their procedures for an oracle database. I can use the update procedures in the packages successfully if the data type is all varchar, but I cannot figure out how to pass a number to a number parm in the Oracle procedure.

I am getting the error:
"Cannot bind type System.Int32 as Blob. SystemInvalidCastException"


Here is the vb.net code I'm using:
Dim dbcmbwrap9 As Oracle.OracleCommandWrapper = dbora.GetStoredProcCommandWrapper"BANKING.withdrawFromAcct")
dbcmbwrap9.AddInParameter("p_acno", OracleType.VarChar, "A100")
dbcmbwrap9.AddInParameter("p_amt", OracleType.Number, 100)
dbora.ExecuteNonQuery(dbcmbwrap9)

My oracle spec looks like:
Procedure withdrawFromAcct (p_acno IN accounts.acno%type,
p_amt In number);

I have written ADO.net code to use the above oracle spec and it works.

Does anyone know what OracleType I need to set to get the number to oracle or what other changes I need to make to have it work?

Thank you.
Desperate in Toledo
Sherry
Apr 20, 2007 at 3:05 PM
Well, I don't code directly against the OracleType, I use the EntLib's DbType instead. And I also use a code generator to automate the process, but here is an example of how I would call an Oracle stored procedure.

Public Shared Sub OSPTDSLS969_UPD(ByVal COMP_IN As Decimal, ByVal REFA_IN As String, ByVal CUNO_IN As String, _
		ByVal TRDT_IN As DateTime, ByVal TRTM_IN As Decimal, ByVal SNAMA_IN As String, ByVal SNAMB_IN As String, _
		ByVal SNAMC_IN As String, ByVal SNAMD_IN As String, ByVal SNAME_IN As String, ByVal SNAMF_IN As String, _
		ByVal SPSTC_IN As String, ByVal SCCTY_IN As String, ByRef ERROR_CODE As Decimal, ByRef ERROR_MSG As String)
 
	' Create the Database object, using the default database service. The
	' default database service is determined through configuration.
	Dim db As Database = DatabaseFactory.CreateDatabase()
 
	Dim sqlCommand As String = "OSPTDSLS969_UPD"
	Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
	
	' Add procedure parameters
	db.AddInParameter(dbCommand, "COMP_IN", DbType.Decimal, COMP_IN)
	db.AddInParameter(dbCommand, "REFA_IN", DbType.AnsiStringFixedLength, REFA_IN)
	db.AddInParameter(dbCommand, "CUNO_IN", DbType.AnsiStringFixedLength, CUNO_IN)
	db.AddInParameter(dbCommand, "TRDT_IN", DbType.DateTime, TRDT_IN)
	db.AddInParameter(dbCommand, "TRTM_IN", DbType.Decimal, TRTM_IN)
	db.AddInParameter(dbCommand, "SNAMA_IN", DbType.AnsiStringFixedLength, SNAMA_IN)
	db.AddInParameter(dbCommand, "SNAMB_IN", DbType.AnsiStringFixedLength, SNAMB_IN)
	db.AddInParameter(dbCommand, "SNAMC_IN", DbType.AnsiStringFixedLength, SNAMC_IN)
	db.AddInParameter(dbCommand, "SNAMD_IN", DbType.AnsiStringFixedLength, SNAMD_IN)
	db.AddInParameter(dbCommand, "SNAME_IN", DbType.AnsiStringFixedLength, SNAME_IN)
	db.AddInParameter(dbCommand, "SNAMF_IN", DbType.AnsiStringFixedLength, SNAMF_IN)
	db.AddInParameter(dbCommand, "SPSTC_IN", DbType.AnsiStringFixedLength, SPSTC_IN)
	db.AddInParameter(dbCommand, "SCCTY_IN", DbType.AnsiStringFixedLength, SCCTY_IN)
	db.AddOutParameter(dbCommand, "ERROR_CODE", DbType.Decimal, 0)
	db.AddOutParameter(dbCommand, "ERROR_MSG", DbType.AnsiString, 0)
	
	db.ExecuteNonQuery(dbCommand)
	
	' Get output parameter values
	db.GetParameterValue(dbCommand, "ERROR_CODE")
	db.GetParameterValue(dbCommand, "ERROR_MSG")
 
End Sub

I believe I read somewhere that all numbers come through the Microsoft Oracle provider as Decimal, so you might try using that as your OracleType.

______________________
Matthew Noonan
EasyObjects.NET -- The O/RM for the Enterprise Library
Apr 20, 2007 at 5:22 PM
Matthew

Thank you so much! There is no OracleType.Decimal, so I tried your DbType.Decimal and it now works.

I am happy!

Sherry