Tsk Tsk Tsk. I put my hands recently on the DAAB 1.1. i had used it before with SQL Server and indeed was a great experience.
I got quite a disappointment when i tried this block against Oracle 9i.
I used System.Data.oracle client as provider to form my OracleCommand and to my surprise I wasn’t able to bind System. Int32 data types to OracleType.Number and the parameters marked with output direction always returned the error of buffer to small to allocate the response.
My hunch is the DBCommandWrapper class of the DAAB 1.1 block has something missing…
I tried using a different provider and downloaded ODP.NET from Oracle site. This will incurr on having an extra dll to deploy with my project, but that didn’t work either.
The scenario to reproduce the errors is as follows:
In the method that calls the SPROC I have the following:
Dim IObject As ICommonFactory
Dim cmd As New OracleClient.OracleCommand
Dim myQuote As New DataSet
Try
‘Write to Trace if Enabled
ServiceHelper.RoutineBegin(Debug)
cmd.CommandText = “web.sproc_request”
cmd.CommandType = CommandType.StoredProcedure
With cmd
.Parameters.Add(“NO”, OracleType.VarChar).Value = “333-333333-333”
.Parameters.Add(“iTEMPLATE”, OracleType.VarChar).Value = “XX_XX_COMBINED_ENGLISH”
Dim parameter As New OracleParameter
parameter.ParameterName = “oXML_STRING”
parameter.OracleType = OracleType.VarChar
parameter.Size = 10000
parameter.Direction = ParameterDirection.Output
.Parameters.Add(parameter)
End With
IObject = ObjectCreator.CreateObject(“MyApp”, “Factory”)
Quote_InitialRequest = IObject.GetList(cmd)
‘Write to Trace if Enabled
ServiceHelper.RoutineEnd(Debug)
Catch ex As Exception
Throw
End Try
The GetList Method of the factory looks like this:
Imports Microsoft.Practices.EnterpriseLibrary.Data
…
Public Function GetList(ByVal cmd As
System.Data.OracleClient.OracleCommand) As System.Data.DataSet Implements ICommonFactory.GetList
Dim db As Database =
DatabaseFactory.CreateDatabase(System.Configuration.ConfigurationSetting
s.AppSettings(“MyApp”), “Common”)
db.ExecuteNonQuery(CommonCmdWrapper.PrepareCommand(db, cmd))
Dim myData As New DataSet
GetList = CType(cmd.Parameters(2).Value, System.Data.DataSet)
End Function
The exception is thrown in the lne db.ExecuteNonQuery and the exception message is as follows:
Message “ORA-06502: PL/SQL: numeric or value
error: character string buffer too small
ORA-06512: at “MyApp.WEB”, line 990
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
” String
The stack trace is as follows:
StackTrace ” at
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle&
rowidDescriptor)
at
System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean
needRowid, OciHandle& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DB
CommandWrapper command)
at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DBCo
mmandWrapper command)
at
CIT.EAI.CS.ECS.OracleObjectFactory.PQSFactory.GetList(OracleCommand cmd) in C:\CIT.EAI.CS.ECS.OracleObjectFactory\PQSDEV\PQSFactory.vb:line 27
at QuoteSystem.QuoteSystem.Quote_InitialRequest(String ContractNo, String TemplateName, String BuyOutType, Boolean Debug) in c:\inetpub\wwwroot\QuoteSystem\QuoteSystem.asmx.vb:line 90″
String
No matter how much I increase the size of the varchars I always have the same error.
I executed the Oracle command in SQL Navigator and it only returns the
ORA-06502: PL/SQL: numeric or value error: character string buffer too small when the output parameter has a size smaller than 2000.
The CommonCmdWrapper is part of the
Microsoft.Practices.EnterpriseLibrary.Data and it seems to be the layer causing the problem.
When using only the System.Data.OracleClient namespace, the call goes through without problems.
I tried contacting the Pattern & Practices team unsuccessfully…