Add ability to disable NCHAR on Oracle engines

[Former user]

For support of legacy databases, even if the database ''supports'' NCHAR, I don't necessarily want to render the create table statements with NVARCHAR() types.

Could we add the ability to disable NCHAR usage? (Some may also wish to disable char_length...)

I used to get around this by manually setting:

  engine.dialect._supports_char_length = False
  engine.dialect._supports_nchar = False

However, these are now @properties:

    @property
    def _supports_char_length(self):
        return not self._is_oracle_8

So my workaround is no good and I need change to public API...

[Mike Bayer]

I'm assuming your app specifies NCHAR in the first place ? otherwise it wouldn't be rendering NCHAR.

Override the compilation as you like:

http://www.sqlalchemy.org/docs/reference/ext/compiler.html#changing-compilation-of-types

this is likely 'worksforme'

[Former user]

My app specifies Unicode() columns (it may be run against postgresql, oracle 8i or higher). There is no simple way to tell the engine "I don't want you to use NCHAR for Oracle 9i, even though 9i is capable of such", is there? (For postgres I'd like it to behave as normal).

Your suggestion is to override Unicode & UnicodeText

@compiles(Unicode, 'oracle'):

@compiles(UnicodeText, 'oracle'):

Could instead reassign __visit_name__:

# at app start up...
if dialect.name == 'oracle':
  Unicode.__visit_name__ = 'VARCHAR'
  UnicodeText.__visit_name__ = 'CLOB'

Or would that cause problems?

[Mike Bayer]

Unicode() resolves to VARCHAR and/or NVARCHAR. It wouldn't output NCHAR in any situation. Or are you using the terms "NVARCHAR"/"NCHAR" interchangeably ?

Also, I know that some earlier versions of oracle which support NVARCHAR, but the NVARCHAR doesn't actually work with cx_oracle. I though I had ensured that those versions would still use VARCHAR. Is that not the case here ?

[Former user]

Replying to zzzeek:

Unicode() resolves to VARCHAR and/or NVARCHAR. It wouldn't output NCHAR in any situation. Or are you using the terms "NVARCHAR"/"NCHAR" interchangeably ?

Also, I know that some earlier versions of oracle which support NVARCHAR, but the NVARCHAR doesn't actually work with cx_oracle. I though I had ensured that those versions would still use VARCHAR. Is that not the case here ?

Sorry, I was speaking of NCHAR more generally to include NCLOB/NVARCHAR.

We did go through and make sure that oracle 8 wouldn't attempt to use NVARCHAR for unicode. That isn't my issue. In my case, even though I know an Oracle 9i or 10g database supports NVARCHAR, I don't want to use it because the database tables are already defined as VARCHAR and I want to build my tables such that UNIONS will work with matching types.

The "real" solution is to use String() instead of Unicode() in my Column() definitions, but when running on postgres, I want the Unicode(). So, for me, I want to keep the column definitions Unicode().

[Former user]

I see VisitableType is a Metaclass, so apparently _compiler_dispatch is already resolved to visit_unicode and my changing Unicode.visit_name = 'VARCHAR' doesn't affect anything.

I'll get this to work with @compiles or something, feel free to mark 'worksforme'

Thanks, Kent

[Mike Bayer]

Oh, is this your polymorphic unions ? is SQLA outputting a CAST or something ? I'm just curious what the actual issue is.

Anyway yes, you can stick a @compiles(Unicode, 'oracle') on a function and then have it return compiler.visit_VARCHAR(type). three lines:

@compiles(Unicode, 'oracle')
def compile_unicode(element, compiler, **kw):
    return compiler.visit_VARCHAR(element)

[Former user]

Replying to zzzeek:

Oh, is this your polymorphic unions ? is SQLA outputting a CAST or something ? I'm just curious what the actual issue is.

No, although that is still to come (I think once I test that polymorphic union on the ora 8i database the CAST is going to fail, but I'll let you know when I get there.)

This is a actually a case where I am building an oracle VIEW that is a UNION of my table with a select statement from the legacy database table, which has VARCHAR columns... Oracle complains if one part of the union is NVARCHAR and one is VARCHAR. I could probably work around it for this particular UNION, but my fear has been that it will just keep coming up again until I make all my tables uniformly VARCHAR instead of NVARCHAR. That might not be the case, but I didn't want to deal it anymore since I had already struggled with it enough by then.

The idea was it might be nice to be able to say:

Table('table' metadata,
    Column("col", Unicode(50)
)

and have it work with unicode support in postgres and also choose to render the column as VARCHAR instead of NVARCHAR by setting an oracle engine dialect flag.

However, in the end, the @compiles is equivalent.