Quoting breaks sequence queries in Oracle

[Former user]

In SQLAlchemy 4.0b4, create a model with a Sequence in its column against the Oracle backend. Create the table, then create an object of the model and save it. The SQL emitted will not be recognized by Oracle: sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-02289: sequence does not exist 'SELECT "myschema.seq_foobar".nextval FROM DUAL'

This error is actually due to the fact that SQLAlchemy added the double-quotes. Removing them makes Oracle run the same SQL without error: SELECT myschema.seq_foobar.nextval FROM DUAL;

This worked fine in SQLAlchemy 3, which we'll have to keep using for now.

[Mike Bayer]

thats interesting, what version of oracle ? Im pretty sure i tested this release against my local Oracle XE.

we'll have to make the quoting conditional based on identifier casing.

[Former user]

Patch to fix Oracle sequence quoting problem.

[Former user]

I see the same behavior against Oracle 9i and 10g:

SELECT "cryoinv_stack_sq".nextval FROM dual

ERROR at line 1: ORA-02289: sequence does not exist

SELECT cryoinv_stack_sq.nextval FROM dual

NEXTVAL -- 63

I patched it crudely by adding this method to the OracleIdentifierPreparer class in oracle.py:

def format_sequence(self, sequence):
    return sequence.name

I attached that as a patch, but I'm not that familiar with the SQLAlchemy codebase and have a strong suspicion this isn't the optimal fix.

[Mike Bayer]

I cant duplicate the behavior of the "cryoinv_stack_sq" description; the other issue is due to the sequence name being munged with the schema in which it resides. This is partially a dupe of #584. As such, Ive partially implemented #584 by adding the "schema" argument to Sequence; this in 32dcfdf80801051971ce9638a1d9292262b375aa. So as of trunk/0.4beta6, implement your sequence like the following:

Sequence("foo_bar", schema="my_schema")

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)