change oracle dialect to use char_length instead of length for reflection

Currently when determining the size of a CHAR, VARCHAR2 or NVARCHAR2 column, we are using the "data_length" information from ALL_TAB_COLUMNS, however, for unicode databases, or those with non-ascii character sets, this number does not actually represent the number of characters that fit in the column, but rather the number of bytes. The number of characters is stored in the "char_length" column.

Here are the changes I think solve the problem(all in dialects.oracle.base:

    @reflection.cache
    def get_columns(self, connection, table_name, schema=None, **kw):
        """

        kw arguments can be:

            oracle_resolve_synonyms

            dblink

        """
        .
        .
        .
        c = connection.execute(sql.text(
                "SELECT column_name, data_type, char_length, data_precision, data_scale, "
                "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "
                "WHERE table_name = :table_name AND owner = :owner " 
                "ORDER BY column_id" % {'dblink': dblink}),
                               table_name=table_name, owner=schema)
        .
        .
        .




    def visit_VARCHAR(self, type_):
        return "VARCHAR(%(length)s CHAR)" % {'length' : type_.length}

    def visit_NVARCHAR(self, type_):
        return "NVARCHAR2(%(length)s CHAR)" % {'length' : type_.length}

If we could get this changed into the 0.6 release that would be great.

The CHAR column definition syntax has been around since oracle 9i, but is not supported in 8 and earlier, so I'm unsure as to how to best handle that difference for pre 9i databases.

Comments (10)