Forum:  Designer

Thread:  Design First: for Oracle decimal field

JayBee (User)   Posted on: 05-Dec-2011 17:28:18.

I'm trying to design some tables holding financial amounts.
What fieldtype definition should I use to have the create script define these amounts as NUMBER(12, 2)?

Currently it generates a float.
I am using the ODP.NET driver.

Best regards,

daelmo (Support Team)   Posted on: 06-Dec-2011 06:15:41.
If you set the entity field's type to Decimal (scale:12, precision:2) it should generates NUMBER(12,2) for the DDL script. You can confirm that when you auto-map unmapped fields, you will see that field is mapped to number(12,2) in the 'Field mappings' tab of the entity.

JayBee (User)   Posted on: 08-Dec-2011 11:17:33.
Doesn't work for me.
I have attached an example.

Using autmapping of entities or fields does not result in adding the numeric field. This only happens if I keep the default value for a decimal field (precision 18 ). But if I change the value into 12 and scale 2, I get a mapping error if I verify and adjust the relational modal data.
Walaa (Support Team)   Posted on: 08-Dec-2011 12:50:44.
ODP.NET maps Numbers to .NET Decimals starting from Percision 16.

So if you create a field with a decimal Type of (16,2), then you can automap it to Number(16,2).

JayBee (User)   Posted on: 08-Dec-2011 13:17:11.

I am using ODP.NET. Defining a field in LLBLGEN the way you describe gives an error when LLBLGEN automaps it. In the second posting I have added a projectfile.
Please check if you can get it to work.
It is a simple example: 1 entity with 2 fields (identifier and a numeric field).


Walaa (Support Team)   Posted on: 09-Dec-2011 08:52:25.
I did use your test project. And it worked with me.
I created a new decimal model field with Preceision of 16 and scale of 2. And used automaping, and there were no errors.

NUMBER(12, 2) maps to double in ODP.NET. As Double has no precision/scale, it's not possible to create a table field from that. It's sad, but ODP.NET is mapping NUMBER(x, y) to double if 8<x<16 and y > 0.

JayBee (User)   Posted on: 09-Dec-2011 12:06:37.
After doing the automapping, did you check the generated field?
When I try it, automapping does generate the table definition for ODP.NET but the field is missing. The field is only added to the table definition when I do not specify the precision and scale. Then a definition for a FLOAT variable with precision 18 is generated.
When I then change the definition at the LLGLGen side into Precision 12 and Scale 2, automapping the fields or use the Validate and adjust relational data model function results in errors.
Otis (LLBLGen Pro Team)   Posted on: 09-Dec-2011 12:13:52.
The mappings in the end have to match what ODP.NET creates from the values read from the DB. This means that a NUMBER(12,2) will result in a double. However, more Oracle types result in a double. So doing model first development, for some .NET types, there are several DB types possible. The driver has a rule set from which it picks the right Oracle type, if possible. Unfortunately, there's no .NET type which will result in a NUMBER(12,2), if you specify Decimal (18, 2), it will result in NUMBER(18, 2), as NUMBER(18, 2) will result in a Decimal as well. However, as NUMBER(12, 2) results in a double in ODP.NET, you can only state it as .NET type 'double' in the entity field, but ... that leads to a float, not a NUMBER(12,2) as double doesn't have a precision/scale. Dissapointed

I know this is unfortunate, but it's a result of the fact the types don't map 1:1 onto each other. You can change the type of the resulting field though: in the entity mappings, select the field, and click the 'edit field', which will allow you to change the table field's type specifics.

Does this solve your problem?

JayBee (User)   Posted on: 09-Dec-2011 13:26:12.
No matter what value I put into Precision and Scale, the field will not be added to the ODP.NET definition.
When I change from ODP.NET to MS ORACLE the decimal field is added, but the Identifier field is not mapped.

I am using 3.1 final, released on the 30th of september, running on MS Server 2008 R2 service pack 1 in 64-bit mode.
Walaa (Support Team)   Posted on: 12-Dec-2011 08:45:02.
Could you please start with a new entity (for the sake of testing).
- Create a Decimal field, with (16, 2).
- Then go to the field mapping tab, and select the field and press autoMap.

JayBee (User)   Posted on: 12-Dec-2011 13:10:58.
For ODP.NET decimal(16,2) results in NUMBER(16,2). However decimal(12,2) does not give an error but is also not mapped. I would expect either an error or a mapping to a float, as Frans suggested.

For the MS ORACLE driver decimal(12,2) results in NUMBER(12,2) but the Identifier field is not mapped.

I have attached a pdf with some screen prints.

Walaa (Support Team)   Posted on: 12-Dec-2011 15:36:25.
I think we were speaking about 2 different scenarioes.
I was speaking about using the "Auto-new Field" button in the "Field Mapping" Sub-Tab.

Please check the attached pic.(attaching)

JayBee (User)   Posted on: 12-Dec-2011 20:08:57.

That works fine. To me this was not obvious.