Hi,

We have a legacy database with lot of business logic placed in stored procedures. When we generates the DAL layer we encounter problem with the parameters to the stored procedures. They are specified as NUMBER (unconstrained) so they are able to accept decimal number as well as integer. But the code generator produce parameter types with precision 22 and scale 0:

parameters[0] = new OracleParameter("PWEIGHT1", OracleDbType.Decimal, 0, ParameterDirection.Input, true, 22, 0, "", DataRowVersion.Current, pweight1);

meaning big integer type so no decimal values are being saved - just truncated to integers. Found a thread about it and you stated that no meta data are given so LLBLGen just translate it to that type. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14405

First question: Is it possible to somehow control the code generation tool to specify the precision and scale for stored procedures? We have a lot of procedures to call...

We also tried to change the stored procedure´s parameter type to the table's type (FLOAT) it is working on to try work around the problem with meta data. But this resulted in following truncate exception when calling the stored procedure from LLBLGen. It seems that LLBLGen translated the type to precision 38, scale 127 - but if we look at the entity directly it has precision 38, scale 38.

So it seems to be a problem with Oracle FLOAT parameter types? How should we proceed?

We are running LLBLGen 2.6 Final September 12th, 2008, Oracle 10g/11g Driver (ODP.NET 10.2.x) Driver version 2.6.08132008

Type: Oracle.DataAccess.Types.OracleTruncateException, kapat resultat

StackTrace: at Oracle.DataAccess.Types.OracleDecimal.ConvertToPrecScale(OracleDecimal value1, Int32 precision, Int32 scale) at Oracle.DataAccess.Client.OracleParameter.PreBind_Decimal() at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at LFS.DAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, OracleParameter[] parameters) in C:\Project\LFS\Source\LFS.DAL\DatabaseSpecific\DataAccessAdapter.cs:line 177 at LFS.DAL.DatabaseSpecific.ActionProcedures.NewtwowirefeederTwowirefeedercharged(Decimal pweight1, Decimal pweight2, Decimal pweight3, Decimal pweight4, Decimal pweight5, Decimal pweight6, Decimal pweight7, Decimal pweight8, DataAccessAdapter adapter) in C:\Project\LFS\Source\LFS.DAL\DatabaseSpecific\ActionProcedures.cs:line 7749 at LFS.BL.Managers.BinMgr.ExecuteNewtwowirefeederTwowirefeedercharged(WireFeedingData data) in C:\Project\LFS\Source\LFS.BL\Managers\BinMgr.cs:line 308 at LFS.BL.Managers.BinMgr.RegisterWireFeedingData(WireFeedingData data) in C:\Project\LFS\Source\LFS.BL\Managers\BinMgr.cs:line 71 at LFS.Events.ProcessEvents.WireFeedingEndedEvent.OnEventArrived() in C:\Project\LFS\Source\LFS.BL\Events\ProcessEvents\WireFeededEndedEvent.cs:line 49 at LFS.BL.Managers.EventMgr.OnEventArrived(ILFSEvent evt) in C:\Project\LFS\Source\LFS.BL\Managers\EventMgr.cs:line 20 at LFS.BL.Service.EventQueueService`1.PerformWork(Object stateInfo) in C:\Project\LFS\Source\LFS.BL\Services\EventQueueService.cs:line 133

This is the stored procedure in PL-SQL: PROCEDURE TwoWireFeederCharged(pWeight1 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight2 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight3 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight4 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight5 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight6 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight7 IN LFS_FBD_ADD1.WEIGHT%TYPE, pWeight8 IN LFS_FBD_ADD1.WEIGHT%TYPE) IS ....

This is the table the procedure working on, weight column: CREATE TABLE "LFS"."LFS_FBD_ADD1" ( "HEAT_NUMBER" NUMBER(10,0), "WSTEP" NUMBER(5,0), "ORDER_SEQUENSE" NUMBER(5,0), "MAT_CODE" NUMBER(10,0), "WEIGHT" FLOAT(126), "BIN_ID" NUMBER(5,0), "UPD_DAT" DATE, "MAN_CHARGED" NUMBER(1,0) DEFAULT 0, CONSTRAINT "LFS_FBD_ADD1_PK" PRIMARY KEY ("HEAT_NUMBER", "WSTEP", "ORDER_SEQUENSE") ENABLE ) ;

This is the generated code:

public static int NewtwowirefeederTwowirefeedercharged(System.Decimal pweight1, System.Decimal pweight2, System.Decimal pweight3, System.Decimal pweight4, System.Decimal pweight5, System.Decimal pweight6, System.Decimal pweight7, System.Decimal pweight8, DataAccessAdapter adapter) { OracleParameter[] parameters = new OracleParameter[8]; parameters[0] = new OracleParameter("PWEIGHT1", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight1); parameters[1] = new OracleParameter("PWEIGHT2", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight2); parameters[2] = new OracleParameter("PWEIGHT3", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight3); parameters[3] = new OracleParameter("PWEIGHT4", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight4); parameters[4] = new OracleParameter("PWEIGHT5", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight5); parameters[5] = new OracleParameter("PWEIGHT6", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight6); parameters[6] = new OracleParameter("PWEIGHT7", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight7); parameters[7] = new OracleParameter("PWEIGHT8", OracleDbType.Decimal, 22, ParameterDirection.Input, true, 38, 127, "", DataRowVersion.Current, pweight ; int toReturn = adapter.CallActionStoredProcedure("LFS.NEWTWOWIREFEEDER.TWOWIREFEEDERCHARGED", parameters); ....

Please check the following thread (try using a TypeConverter)? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10266

Walaa wrote:

Please check the following thread (try using a TypeConverter)? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10266

Thanks for fast reply.

This thread seems to be about the same issue, but I couldn't find the solution there.

Is it really possible to use a type converter on a parameter in a stored procedure call? 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 nor choosing a type converter - only the parameter's name. This is different from the typed views and entities. Or do I miss something?

Oh my fault, by the time I reached the end of your first post I had somehow forgot it was about an SP.

We'll check out your issue and get back to you.

Just to make sure, would you please tell me which Oracle DQE version are you using? SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll -> right click -> file version.

Easy done with that heavy post

Yes, of course - it is version 2.6.8.819.

The problem is that Oracle doesn't store precision/scale information in the meta -data for a proc parameter of type NUMBER(x,y), as PL/SQL for proc headers simply ignores precision/scale. This results in a NUMBER(38,0) by default, as there's no info about precision/scale available to the driver, just 'NUMBER'. Oracle uses NUMBER(38,0) for NUMBER without precision/scale.

So it can't make decisions what type to pick other than decimal with 0 scale. The float for parameters issue is likely a bug in the driver, we've seen the same issue with FLOAT fields some time ago.

How to work around this... I have no easy workaround for this, other than perhaps a plugin which resets precision/scale for proc parameters from data it imports, but that's perhaps not that maintainable. ODP.NET is not really that flexible for parameter creation, so if you alter the templates to make every parameter in the method signature to be of type 'object' and then create the parameter without setting precision/scale, it might work, but it's also likely it will fail in some cases.

I'll look into the FLOAT issue for proc parameters.

The FLOAT issue was fixed in the DQE by not setting precision/scale. I'll look into a template change and see if I can also call a proc with NUMBER by not setting precision/scale for decimals.

Thanks for your feedback. I'll wait the result.

Ok, I fixed both issues

Here's my test proc (on SCOTT)

CREATE OR REPLACE PROCEDURE "SCOTT"."pr_FloatTest" (
Foo in FLOAT, DateVal in out Date, EmpId in NUMBER
)
AS
begin
  SELECT HireDate INTO DateVal FROM scott.EMP WHERE EmpNo = EmpId;
end;

Here's my unittest:

[Test]
public void CallProcWithDecimalAndFloats()
{
    DateTime retrievedDate = DateTime.Now;
    ActionProcedures.FloatTest_(1.0M, 1000.0M, ref retrievedDate);
    Assert.AreEqual(2004, retrievedDate.Year);
    Assert.AreEqual(3, retrievedDate.Month);
    Assert.AreEqual(5, retrievedDate.Day);
}

this works, because I now create parameters for procs differently for decimal typed parameters: no precision/scale is set for decimal typed parameters. This should overcome any issue regarding floats and decimal typed parameters (i.e. any NUMBER typed parameter).

It's currently raw code in the generated code, I'll convert it to a changed template now and will attach the changed templates so you can test it.

Done. Check the attached templates.

Place the templates in the folder: Templates\Oracle10gSpecific\Net2.x\C#\ overwrite existing templates.

Then re-generate the code.

Nice! Really good work! I will test it, however, were can I find the attachments?

_andy wrote:

Nice! Really good work! I will test it, however, were can I find the attachments?

I thought I had attached the data. It's attached to my previous post (please click the paperclip)

Nice! Confirmed: Both issues solved! Thanks.

Got a side effect though. With new templates, another procedure with VARCHAR2 as parameter type is now going a little nuts.

Receiving ORA-06502 character string buffer too small.

Message: ORA-06502: PL/SQL: fel i tal eller värde: buffert för teckensträng för liten ORA-06512: vid "LFS.ALERT", rad 38 ORA-06512: vid rad 1, StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at LFS.DAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, OracleParameter[] parameters) in c:\project\lfs\source\lfs.dal\databasespecific\dataaccessadapter.cs:line 177 at LFS.DAL.DatabaseSpecific.ActionProcedures.AlertWaitany(Decimal ptimeout, String& pmessage, String& pname, DataAccessAdapter adapter) in C:\Project\LFS\Source\LFS.DAL\DatabaseSpecific\ActionProcedures.cs:line 344 at LFS.BL.AlertHandler.WaitAny(Int32 timeout) in C:\Project\LFS\Source\LFS.BL\Alerts\AlertHandler.cs:line 49 at LFS.BL.Services.ClientService.HandleEvents() in C:\Project\LFS\Source\LFS.BL\Services\ClientService.cs:line 44

PROCEDURE WaitAny (pTimeOut IN NUMBER, pMessage OUT VARCHAR2, pName OUT VARCHAR2)

Generated code NEW TEMPLATE: OracleParameter[] parameters = new OracleParameter[3]; parameters[0] = ParameterCreator.Create("PTIMEOUT", OracleDbType.Decimal, ParameterDirection.Input, 22, 0, ptimeout); parameters[1] = ParameterCreator.Create("PMESSAGE", OracleDbType.Varchar2, ParameterDirection.Output, 0, 0, pmessage); parameters[2] = ParameterCreator.Create("PNAME", OracleDbType.Varchar2, ParameterDirection.Output, 0, 0, pname);

Generated code OLD TEMPLATE: OracleParameter[] parameters = new OracleParameter[3]; parameters[0] = new OracleParameter("PTIMEOUT", OracleDbType.Decimal, 0, ParameterDirection.Input, true, 22, 0, "", DataRowVersion.Current, ptimeout); parameters[1] = new OracleParameter("PMESSAGE", OracleDbType.Varchar2, 4000, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, pmessage); parameters[2] = new OracleParameter("PNAME", OracleDbType.Varchar2, 4000, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, pname);

Hmm... the length isn't set, I see. The strange thing is... in the DQE parameter creation routine, this is also not set... Looking into it.

Fixed in attached templates. The Size isn't set in normal parameters, but I guess it's not important for the part of the CLI which handles dyn. SQL queries apparently, and with procs, the CLI gives up. Anyway, please let us know if this has other side effects for you.

That solved it! Thanks. However, it seems another problem is now raised.

With a call to a stored procedure which returns data with out parameters, in some cases - not always - a System.OverflowException is raised, message: Arithmetic operation resulted in an overflow.

at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx) at Oracle.DataAccess.Types.OracleDecimal.get_Value() at LFS.DAL.DatabaseSpecific.ValueConverter.Convert(OracleParameter parameter) in C:\Project\LFS\Source\LFS.DAL\DatabaseSpecific\ActionProcedures.cs:line 26501 at LFS.DAL.DatabaseSpecific.ActionProcedures.ExtcommNewprocessstep(Decimal& pemssp, Decimal& pdirection, Decimal& ptapposition, Decimal& pcurvesp, Decimal& pgastype, Decimal& pargonflowsp, Decimal& pspearingmtrlcode, Decimal& pendtemp, Decimal& pnextprocessstep, Decimal& pendtime, Decimal& pspearingweightsp, Decimal& pspearingdipdepth, Decimal& pinjectiondipdepth, Decimal& pheatname, DataAccessAdapter adapter) in C:\Project\LFS\Source\LFS.DAL\DatabaseSpecific\ActionProcedures.cs:line 3136 at LFS.BL.Managers.BinMgr.ExecuteExtcommNewprocessstep() in C:\Project\LFS\Source\LFS.BL\Managers\BinMgr.cs:line 363 at LFS.Events.TestEvents.NewProcessStepEvent.OnEventArrived() in C:\Project\LFS\Source\LFS.BL\Events\ProcessEvents\NewProcessStepEvent.cs:line 41 at LFS.BL.Managers.EventMgr.OnEventArrived(ILFSEvent evt) in C:\Project\LFS\Source\LFS.BL\Managers\EventMgr.cs:line 20 at LFS.BL.Service.EventQueueService`1.PerformWork(Object stateInfo) in C:\Project\LFS\Source\LFS.BL\Services\EventQueueService.cs:line 139

Row 26501 is this generated piece of code: decimal value = valueAsDecimal.Value;

Stored procedure code: PROCEDURE NewProcessStep(pEmsSP OUT NUMBER, pDirection OUT NUMBER, pTapPosition OUT NUMBER, pCurveSP OUT NUMBER, pGasType OUT NUMBER, pArgonFlowSP OUT NUMBER, pSpearingMtrlCode OUT NUMBER, pEndTemp OUT NUMBER, pNextProcessStep OUT NUMBER, pEndTime OUT NUMBER, pSpearingWeightSP OUT NUMBER, pSpearingDipDepth OUT NUMBER, pInjectionDipDepth OUT NUMBER, pHeatName OUT NUMBER) IS ....

(line 26501, that's a lot of stored procedures.... )

The overflow is caused by the fact that a number is returned which doesn't fit in a .NET decimal. .NET decimals have a max precision of 28, Oracle NUMBER has a max precision of 38. The value is originally returned as an OracleDecimal typed value in the parameter, and when it's converted to a .NET typed value (System.Decimal) it fails, as it causes an overflow.

This isn't fixable, other than keeping the OracleDecimal around, which isn't really what you want as you then have to reference the ODP.NET assembly wherever you use the value. So I'd truncate the value inside the proc (if possible).