14 Replies Latest reply on Jun 18, 2012 4:30 PM by DNETFX

    Odac 112030 release 4 - NUMBER type conversion

    888889
      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
          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.
          1 person found this helpful
          • 2. Re: Odac 112030 release 4 - NUMBER type conversion
            888889
            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
              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
                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
                  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
                  1 person found this helpful
                  • 6. Re: Odac 112030 release 4 - NUMBER type conversion
                    888889
                    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
                      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
                        I did it , but i still get those messages.
                        • 9. Re: Odac 112030 release 4 - NUMBER type conversion
                          DNETFX
                          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
                            @shsu Please see my response to the asker.
                            • 11. Re: Odac 112030 release 4 - NUMBER type conversion
                              931245
                              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 - Product Manager-Oracle
                                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
                                  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
                                    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>