none
MaxLength misreported from System.Data.OracleClient with AL32UTF8 database RRS feed

  • Question

  • Hi everybody,

    I have the following problem in a .NET Framework 1.1 application running against an Oracle 10.2 database with Unicode AL32UTF8 database character set. The tables in the database are created using NLS_LENGTH_SEMANTICS=CHAR. The problem is that in the application, the length of the fields are misreported as being 4 times longer, so that the users can input too much data in the UI which can then not be stored. For example with a field created as VARCHAR2(20 CHAR), the column MaxLength in the application becomes 80 characters.

     

    I suspect this is because System.Data.OracleClient doesn't implement proper support for character length semantics, so the reported lengths are the maximum number of bytes that may be required to store the characters. The application can read and write data including international characters but the reported field lengths are incorrect. I don't know much about the low-level Oracle OCI details but when scanning the documentation I found some information about OCI_ATTR_CHAR_SIZE which is what I suppose the application should use to get the character length of a field.

     

    I made a quick test program using the Oracle Data Provider for .NET and with that it seems to report the column lengths correctly, but changing to that is not something that can be done quickly.

     

    I would be very interested to know if anyone else has encountered similar issues, and if there was a solution that did not involve changing to another database driver such as ODP.NET?

     

    Below is a sample program which can be used to reproduce the problem using both the 1.1 and 2.0 versions of the .NET Framework. Thank you very much in advance for any input you can give on this issue.

     

    Robert

    Code Snippet

     

    using System;
    using System.Data;
    using System.Data.OracleClient;


    namespace OraDbg
    {
        class Class1
        {
            static void Main(string[] args)
            {
                DataSet ds = new DataSet("ds");

                OracleConnection conn = new OracleConnection(String.Format(

                  "Server={0};User ID={1};Password={2}", new String[] {
                  "<servername>",
                  "<user>",
                  "<password>" }));


                OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM TABLE", conn);

                conn.Open();

                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                da.Fill(ds);

                conn.Close();

                foreach (DataColumn dc in ds.Tables[0].Columns)
                    Console.WriteLine(String.Format("{0} len={1}",

                        dc.ColumnName, dc.MaxLength));
            }
        }
    }

     


    Monday, February 11, 2008 9:41 AM