Tuesday, August 08, 2006

DAAB 1.1 notes

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...