oracle startswith etc fails

[kthhrv]

OracleDBModel.objects.filter(name__startswith='abc')

DatabaseError: ORA-01425: escape character must be character string of length 1

changing
django/db/backends/oracle/base.py

-'startswith': "LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE('\\' USING NCHAR_CS)",
+'startswith': "LIKE %s ESCAPE '\\'",

appears to fix the problem

[kthhrv]

initial post stole my formatting

OracleDBModel.objects.filter(namestartswith='abc')

DatabaseError?: ORA-01425: escape character must be character string of length 1

changing django/db/backends/oracle/base.py
-'startswith': "LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE('
' USING NCHAR_CS)",
+'startswith': "LIKE %s ESCAPE '
'", appears to fix the problem

[Karen Tracey]

Fixed formatting. Please use WikiFormatting and Preview before hitting Submit.

[Alex Gaynor]

What version of oracle ar eyou using?

[Erin Kelly]

Also, what is the type of the database column: NVARCHAR2 or VARCHAR2?

For reference, the line currently reads the way it does because of tickets #5985 and #11017.

[kthhrv]

column is 'NUMBER' (models.IntegerField) but it doesn't appear to work on a column thats 'VARCHAR2' (models.CharField) either

using oracle instant_client version 10.2
oracle server looks like 9

[Erin Kelly]

Can you be more specific about the server version? It should be displayed when you connect with sqlplus.

If this is 9.2.0.5 or earlier, then I think this case is the inverse of the problem seen in #11017 -- combining a database charset column with an NLS pattern rather than the other way around. Unfortunately, I don't know how to write the query such that it works regardless of the column type in those early Oracle 9 releases. It is currently written for NVARCHAR2, which is the common case since that is the type created by syncdb for a CharField. To be accepted, a patch would have to fix this case without breaking that one.

I suggest doing one of the following:

• Apply the patch suggested earlier in this ticket to your local Django installation.

• Upgrade your server version to a more recent release.

• Change the column to NVARCHAR2.

• Suggest a way to make this work regardless of column type.

I'm tentatively closing this ticket as wontfix, but please reopen it if can propose a viable solution or if my assumption about the database version is incorrect.