This discussion is archived
14 Replies Latest reply: Jun 18, 2012 9:30 AM by DNETFX RSS

Odac 112030 release 4 - NUMBER type conversion

888889 Newbie
Currently Being Moderated
Hi,

I downloaded the latest odac version - 112030 release 4 .
I see that it converts NUMBER(5,0) to int16. There is a little problem -
NUMBER(5,0) can be 99999. int16 can be 32767. So if the field in oracle table contain a value of 40000 i get an error.
The same thing with NUMBER(10,0) and int32 -
NUMBER(10,0) max is 9,999,999,999
int32 max is 2,147,483,647 so a value of 2,500,000,000 will cause an error.

Is solve this problem like this :
<oracle.dataaccess.client>
<settings>
<add name="int16" value="edmmapping number(4,0)"/> <!-- max NUMBER lenth for int16. number(5,0) will be int32 -->
<add name="int32" value="edmmapping number(9,0)"/> <!-- max NUMBER lenth for int32. number(10,0) will be long -->
</settings>
</oracle.dataaccess.client>

My question is Why the default is wrong ? Is this a bug or there is a reason to do so?

Thanks,

David
  • 1. Re: Odac 112030 release 4 - NUMBER type conversion
    15208 Explorer
    Currently Being Moderated
    This is debatable.
    If map Int16 to number(4, 0), what if an application inserts or updates with an Int16 value from 10000 to 32767?
    When map Int16 to number(5, 0), there is no insert/update issue. As for retrieval, if the column value is greater than
    Int16 maximum (32767), an application can use casting (to Int32) or GetInt32 to get the data.
  • 2. Re: Odac 112030 release 4 - NUMBER type conversion
    888889 Newbie
    Currently Being Moderated
    Hi shsu,

    Thanks for your reply.
    Few things about your post :

    1. I have to know the tables I work with so if the field type is number(4,0) I'll not try to insert/update a number of 5 digits.
    2. About the casting to int32 : How can I cast to int32 if select an entire entity . for example :
    var a = (from x in entities1.employees
    where x.ID == 1234567
    select x).FirstOrDefault();

    This throw an exeption on the default mapping because I have there a number(5,0) fileld that have a value greater than 32767.
    How can I do a cast to int32 on this query ?
  • 3. Re: Odac 112030 release 4 - NUMBER type conversion
    15208 Explorer
    Currently Being Moderated
    About casting from Int16 to Int32, is something like following (based on your example).

    var a = (from x in entities1.employees
    where x.ID == 1234567
    select new {col_1 = x.ID, col_2 = (Int32?) x.ColumnMappedToInt16, col_3 = x.XYZ, ...}).FirstOrDefault();

    Where ColumnMappedToInt16 is NUMBER(5) and has values greater than Int16.MaxValue 32767.
  • 4. Re: Odac 112030 release 4 - NUMBER type conversion
    888889 Newbie
    Currently Being Moderated
    Thanks shsu.
    The problem is when i have to select 30 fields and just one of them is mapped to int16, i have to write all the 30 in the select new {...}
    Do you have a solution for that situation ?
    Anyway,I'll consider to take your advice because it uses the default behavior of the odac and don't need to change behavior in the app.config which not always works like i"ll describe below.

    I have another question -
    Yesterday it was all compiled and work well, but now i get again the Error 2019: Member Mapping specified is not valid.but the compiler says that the build succeeded. When I run the app i got this error at run time.
    I checked the build messages and I found this for the app.config
    Could not find schema information for the element 'oracle.dataaccess.client' for this section
    <oracle.dataaccess.client>
    <settings>
    <add name="int16" value="edmmapping number(4,0)" />
    <add name="int32" value="edmmapping number(9,0)" />
    </settings>
    </oracle.dataaccess.client>
    and so on for other elements - setting, add, name,value
    So now it's not matter if I write this section in the app.config or not, i get the same error.

    What's wrong now ?
  • 5. Re: Odac 112030 release 4 - NUMBER type conversion
    15208 Explorer
    Currently Being Moderated
    In the case you described, using custom type mapping to may int16 to number(4, 0) to generate your data model may be better. This all depends on your application design.

    If a data model is generated by the custom typ mapping specified in app.config, it should not have any issue except the following known behavior.

    (copied and pasted from the readme.txt in Beta 2 version)
    3. Tips to Resolve Compilation Errors With Custom Mapping

    When custom mapping in a configuration file has changed, re-generate the data
    model to solve compilation errors incurred by the new changes.

    In some scenarios, custom mapping can cause compilation errors, when a
    project that uses custom mapping is loaded by Visual Studio. There are
    few ways to resolve the compilation errors:

    (a) Open Visual Studio Help/About Microsoft Visual Studio and click OK
    button to exit the dialog box or

    (b) Open the to-be-used connection in Server Explorer

    Then compile the project again to eliminate the compliation errors.

    (end paste)

    It's recommended that <oracle.dataaccess.client></oracle.dataaccess.client> section is placed after <connectionStrings></connectionStrings> section.
    Certainly, both sections must be within <configuration></configuration> section.

    The error you got seems like ODP.NET is not properly configured in machine.config or it's not GACed.
    Can you create a new connection in Server Explorer or in EDM wizard using ODP.NET?
    What's your target platform when build, x86 or x64? Do you have proper installation of ODAC?

    Edited by: shsu on Jan 13, 2012 2:17 PM
  • 6. Re: Odac 112030 release 4 - NUMBER type conversion
    888889 Newbie
    Currently Being Moderated
    Hi,

    Now I have no errors. I just did this :

    +(a) Open Visual Studio Help/About Microsoft Visual Studio and click OK+
    button to exit the dialog box or
    +(b) Open the to-be-used connection in Server Explorer+

    but it didn't solve the problem immediately, and then I comment out an unnecessary connectionString key, and then I saw no errors. This key had the same connectionString value of another used key. I don't know if this was the problem or not but this what I did.
    Now, I tried to Uncomment this key to see if I get again the list of errors i got before but i don't get any error.

    The application works well now but the only thing I can't eliminate is the messages I get at compile time about the elements and attributes in the <oracle.dataaccess.client> element. The messages are :

    Could not find schema information for the element 'oracle.dataaccess.client'
    Could not find schema information for the element 'settings'
    Could not find schema information for the element 'add'
    Could not find schema information for the attribute 'name'
    Could not find schema information for the attribute 'value'

    The <oracle.dataaccess.client> is just after the </connectionStrings> tag and both are in <configuration></configuration>.
    - I can create new connection using EDM wizard using ODP.Net.
    - My target platform is x86 and win xp sp 3 (my local machine)

    How can I remove those messages ?

    Thanks for your reply.

    David
  • 7. Re: Odac 112030 release 4 - NUMBER type conversion
    15208 Explorer
    Currently Being Moderated
    I suggest that keep your code, delete unused edmx and design cs files, remove all entries within
    <connectionStrings></connectionStrings>, then regenerate the data model using your custom type mapping.
    In EDM wizard, provide the same connectionString key name that is being used in your code. Then
    rebuild your application.
  • 8. Re: Odac 112030 release 4 - NUMBER type conversion
    888889 Newbie
    Currently Being Moderated
    I did it , but i still get those messages.
  • 9. Re: Odac 112030 release 4 - NUMBER type conversion
    DNETFX Newbie
    Currently Being Moderated
    The warnings are created because there is no .XSD file with the schema for the Oracle section in the app.config file.

    Deleting and recreating the EDMX and such won't change anything.

    What you need to do is to create an .XSD file that describes the schema behind the Oracle section in the app.config file,
    then tell the users to open the app.config files (both of the assembly with the EDMX and the application's file),
    go into the Properties pane, click the [...] button at the end of the "Schemas" property and then add the new XSD to the list.
  • 10. Re: Odac 112030 release 4 - NUMBER type conversion
    DNETFX Newbie
    Currently Being Moderated
    @shsu Please see my response to the asker.
  • 11. Re: Odac 112030 release 4 - NUMBER type conversion
    931245 Newbie
    Currently Being Moderated
    I'm sorry for asking slilly questions but you can probably see this one coming:

    How do I create the schema for the oracle dataaccess.client section in app.config
  • 12. Re: Odac 112030 release 4 - NUMBER type conversion
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    Data type mappings are described here:
    http://docs.oracle.com/cd/E20434_01/doc/win.112/e23174/featLINQ.htm#sthref297

    Implicit cursor metadata configuration described here:
    http://docs.oracle.com/cd/E20434_01/doc/win.112/e23174/featImplRefCursor.htm#CJADEIEH
  • 13. Re: Odac 112030 release 4 - NUMBER type conversion
    931245 Newbie
    Currently Being Moderated
    User above said:
    The warnings are created because there is no .XSD file with the schema for the Oracle section in the app.config file.
    My question is how do I create the .xsd file. I dont see any information in the links you posted that is relveant. Forgive me if I missed it and kindly direct me to the specific info.

    Thank you.
  • 14. Re: Odac 112030 release 4 - NUMBER type conversion
    DNETFX Newbie
    Currently Being Moderated
    This is the .xsd I created for my own usage (might not be accurate, since I haven't read Oracle's app.config spec!):

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema id="odpnetappconfigmappings" xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xs:complexType name="addtype">
    <xs:attribute name="name" type="xs:string" />
    <xs:attribute name="value" type="xs:string" />
    </xs:complexType>

    <xs:complexType name="settingstype">
    <xs:sequence minOccurs="0" maxOccurs="unbounded">
    <xs:element name="add" type="addtype" />
    </xs:sequence>
    </xs:complexType>

    <xs:complexType name="oracledataaccessclienttype">
    <xs:sequence minOccurs="0" maxOccurs="1">
    <xs:element name="settings" type="settingstype" />
    </xs:sequence>
    </xs:complexType>

    <xs:element name="oracle.dataaccess.client" type="oracledataaccessclienttype" />

    </xs:schema>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points