Opened 7 years ago

Closed 6 years ago

#13436 closed (wontfix)

oracle startswith etc fails

Reported by: kthhrv Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords: oracle
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by Karen Tracey)

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

Change History (7)

comment:1 Changed 7 years ago by kthhrv

Version: 1.1-beta-11.2-beta

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

comment:2 Changed 7 years ago by Karen Tracey

Description: modified (diff)

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

comment:3 Changed 7 years ago by Alex Gaynor

What version of oracle ar eyou using?

comment:4 Changed 7 years ago by Ian 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.

comment:5 Changed 6 years ago by Łukasz Rekucki

Keywords: oracle added

comment:6 Changed 6 years ago by kthhrv

Version: 1.2-beta1.2

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

comment:7 Changed 6 years ago by Ian Kelly

Resolution: wontfix
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top