Opened 5 years ago

Closed 5 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 kmtracey)

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 5 years ago by kthhrv

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Version changed from 1.1-beta-1 to 1.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 5 years ago by kmtracey

  • Description modified (diff)

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

comment:3 Changed 5 years ago by Alex

What version of oracle ar eyou using?

comment:4 Changed 5 years ago by ikelly

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 5 years ago by lrekucki

  • Keywords oracle added

comment:6 Changed 5 years ago by kthhrv

  • Version changed from 1.2-beta to 1.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 5 years ago by ikelly

  • Resolution set to wontfix
  • Status changed from new to closed

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