I have a numeric fields that sets a value of type Decimal (for instance: 1123.34). When i fire a stored procedure with this parameter set, it returns an error (truncated result error).

When i set a breakpoint in ActionProcedures.cs at the storedprocedure i am calling it all fills in the parameters correctly. With the value 1123.34. Then i step into the code and as soon as it gets to toReturn = command.ExecuteNonQuery(); in DataAccessAdapter.CallActionStoredProcedure() it tries to execute the command but fails and returns an exception.

parameters[11] = new OracleParameter("L_CVALUEGR", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, lCvaluegr);

In the database the column is of type FLOAT (with the precision, scale , etc. not set).

Can anybody help me with this problem?

Please check the following threads: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=5732

If your setup is different from that mentioned in the thread, please post your setup info. (ref: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7722)

I have a more recent version of LLBLGEN, so i don't think the thread solution (which states that it's solved in llblgen 2.0) applies to this problem.

• LLBLGEN 2.0.0.0 FINAL

• SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.0.7.424

• at Oracle.DataAccess.Types.OracleDecimal.ConvertToPrecScale(OracleDecimal value1, Int32 precision, Int32 scale)\r\n at Oracle.DataAccess.Client.OracleParameter.PreBind_Decimal()\r\n at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)\r\n at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()\r\n at ABB.Experience.DAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, OracleParameter[] parameters) in C:\Caesar Projects\ABB Experience\ABB Experience Dev\ABBExperienceV2\ABB Experience DAL\DatabaseSpecific\DataAccessAdapter.cs:line 177\r\n at ABB.Experience.DAL.DatabaseSpecific.ActionProcedures.Client(Nullable1 lForecastgr, Nullable1 lForecastkl, Nullable1 lActualkl, Nullable1 lActualgr, Nullable1 lHomeofficekl, Nullable1 lHomeofficegr, Nullable1 lFieldconstructionkl, Nullable1 lFieldconstructiongr, String lProjectstart, String lProjectend, Nullable1 lCvaluekl, Nullable1 lCvaluegr, Nullable1 lTickl, Nullable1 lTicgr, String lLocation, String lCountry, String lCrttype, Nullable1 lSubctrkl, Nullable1 lSubctrgr, String lConstr, String lSubctr, Nullable1 lCapakl, Nullable1 lCapagr, String lProduct, String lProdesc, String lInstrsys, String lVenname, String lTof2, String lTof1, String lToi, String lOffice, String lPrdir, String lPrman, String lCondir, String lRegion, String lScoserv1, String lAddserv2, String lOilgas3, String lPipelines4, String lStudy5, String lLicensor6, String lUtils8, String lOffsites9, String lAux10, Nullable1 lOnshore, Nullable1 lOffshore, String lParcom, String lClient, String lFunctions, String lExproj, Decimal aScoserv, Decimal aFunctions, Decimal aAddserv, Decimal aOilgas, Decimal aPipelines, Decimal aStudy, Decimal aUtils, Decimal aOffsites, Decimal aAux, Decimal aPrdir, Decimal aPrman, Decimal aCondir, Decimal aOffice, Decimal aToi, Decimal aTof1, Decimal aTof2, Decimal aProdesc, Decimal aInstrsys, Decimal aConstr, Decimal aSubctr, Decimal aParcom, Nullable`1 aExcou, DataAccessAdapter adapter) in C:\Caesar Projects\ABB Experience\ABB Experience Dev\ABBExperienceV2\ABB Experience DAL\DatabaseSpecific\ActionProcedures.cs:line 579\r\n at ABBExperienceV2.frmReportFilter.btnShowReport_Click(Object sender, EventArgs e) in C:\Caesar Projects\ABB Experience\ABB Experience Dev\ABBExperienceV2\frmReportFilter.cs:line 510\r\n at System.Windows.Forms.Control.OnClick(EventArgs e)\r\n at System.Windows.Forms.Button.OnClick(EventArgs e)\r\n at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)\r\n at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)\r\n at System.Windows.Forms.Control.WndProc(Message& m)\r\n at System.Windows.Forms.ButtonBase.WndProc(Message& m)\r\n at System.Windows.Forms.Button.WndProc(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)\r\n at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)\r\n at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)\r\n at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms. UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)\r\n at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)\r\n at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)\r\n at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)\r\n at ABBExperienceV2.frmMain.ultraToolbarsManager1_ToolClick(Object sender, ToolClickEventArgs e) in C:\Caesar Projects\ABB Experience\ABB Experience Dev\ABBExperienceV2\frmMain.cs:line 308

• Adapter .NET 2.0

• Oracle Data Provider for .NET 10.1.0.4.0 Production || Oracle 10

Would you please try using ODP.NET 10.2.0.2, and see if the issue is reproduced?

Also what's the exact error message?

Could you post the proc signature (name + variable names declaration, I don't need the proc body) ?

Btw, how do you set the decimal value? Be aware that 1134.24 isn't a decimal, it's a long:

Decimal d = 1134.24;

will give an error, you have to do: Decimal d = 1134.24M;

But as your code compiles, you seem to set it to the proper dec value

The stacktrace suggests that the error is BEFORE the proc is called, so the parameter binding process already gives up.

I thought LLBLGEN needed ODP.NET 10.1 to work?

Is there anyway to see what the exact sql statements are that are being fired at the Oracle database?

Otis wrote:

Also what's the exact error message?

Could you post the proc signature (name + variable names declaration, I don't need the proc body) ?

Btw, how do you set the decimal value? Be aware that 1134.24 isn't a decimal, it's a long:

Decimal d = 1134.24;

will give an error, you have to do: Decimal d = 1134.24M;

But as your code compiles, you seem to set it to the proper dec value

The stacktrace suggests that the error is BEFORE the proc is called, so the parameter binding process already gives up.

// Example how the first parameter gets set. Decimal? lForecastgr = null; lForecastgr = Decimal.Parse(l_forecastgr.Value.ToString());

public static int Aux(Nullable<System.Decimal> lForecastgr, Nullable<System.Decimal> lForecastkl, Nullable<System.Decimal> lActualkl, Nullable<System.Decimal> lActualgr, Nullable<System.Decimal> lHomeofficekl, Nullable<System.Decimal> lHomeofficegr, Nullable<System.Decimal> lFieldconstructionkl, Nullable<System.Decimal> lFieldconstructiongr, System.String lProjectstart, System.String lProjectend, Nullable<System.Decimal> lCvaluekl,
Nullable<System.Decimal> lCvaluegr, Nullable<System.Decimal> lTickl, Nullable<System.Decimal> lTicgr, System.String lLocation, System.String lCountry, System.String lCrttype, Nullable<System.Decimal> lSubctrkl, Nullable<System.Decimal> lSubctrgr, System.String lConstr, System.String lSubctr, Nullable<System.Decimal> lCapakl,
Nullable<System.Decimal> lCapagr, System.String lProduct, System.String lProdesc, System.String lInstrsys, System.String lVenname, System.String lTof2, System.String lTof1, System.String lToi, System.String lOffice, System.String lPrdir, System.String lPrman,
System.String lCondir, System.String lRegion, System.String lScoserv1, System.String lAddserv2, System.String lOilgas3, System.String lPipelines4, System.String lStudy5, System.String lLicensor6, System.String lUtils8, System.String lOffsites9, System.String lAux10,
Nullable<System.Decimal> lOnshore, Nullable<System.Decimal> lOffshore, System.String lParcom, System.String lClient, System.String lFunctions, System.String lExproj, System.Decimal aScoserv, System.Decimal aFunctions, System.Decimal aAddserv, System.Decimal aOilgas, System.Decimal aPipelines,
System.Decimal aStudy, System.Decimal aUtils, System.Decimal aOffsites, System.Decimal aAux, System.Decimal aPrdir, System.Decimal aPrman, System.Decimal aCondir, System.Decimal aOffice, System.Decimal aToi, System.Decimal aTof1, System.Decimal aTof2,
System.Decimal aProdesc, System.Decimal aInstrsys, System.Decimal aConstr, System.Decimal aSubctr, System.Decimal aParcom, Nullable<System.Decimal> aExcou, DataAccessAdapter adapter)

Most of the decimals have the NUMBER type in oracle. Some of them have the FLOAT type. All of them are mapped to decimal by LLBLGEN generator. The ones that have a FLOAT type in the database give me the error i mentioned. The ones that have a DECIMAL in the database don't give me the error, but they don't seem to be passed through as my selections aren't returned correctly.

I am quite stuck now and can't seem to figure out how to solve this within LLBLGEN generated code.

Some more output:

'ABBExperienceV2.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\ 10.2.3600.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll', No symbols loaded. 'ABBExperienceV2.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Shared\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Shared.dll', No symbols loaded. 'ABBExperienceV2.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Windows.Forms\ 10.2.3600.0__692fbea5521e1304\CrystalDecisions.Windows.Forms.dll', No symbols loaded. A first chance exception of type 'System.InvalidOperationException' occurred in System.dll A first chance exception of type 'Oracle.DataAccess.Types.OracleTruncateException' occurred in Oracle.DataAccess.dll The thread 0xb7c has exited with code 0 (0x0). The thread '<No Name>' (0xf04) has exited with code 0 (0x0). The program '[5412] ABBExperienceV2.vshost.exe: Managed' has exited with code 0 (0x0).

I have solved this by changing the signatures in ActionProcedures.cs. The mapping of floats are now being mapped to double.

As to the numbers being mapped to decimal and not working correctly. That was a mistake in the code.

Can you tell me how i can change the mapping of fieldtypes from the Oracle field types to the types mapped by LLBLGEN in the stored procedure? I have read something about TypeConverters, but can't seem to find any code that explains this in practice.

Thanx in advance.

Hello,

in the designer you can modify the .NET type of yours parameters of your stored procedure without modifying the generated code.

Can you be more specific as to where i can do this? Maybe a screenshot?

Because when i right click on the stored procedure and choose open in editor, i can see all the parameters of the stored procedures, but the fields in which i can change the types don't let me change the content.

Hello,

I looked at the type convertor but the typeconvertor implemented is the BooleanNumericConvertor. So you have to create your own. To do that, you have the procedure in the LLBLGenPro SDK Documentation->Implementing a type convertor. You have the source code of the BooleanNumericConvertor in %LLBLGenPro Directory%\Sourcecode\TypeConverters\Net2.x So you can create a modifying version of it and use it in the designer. To have these files you need to download the sdk.