[oracle] ORA-01425: escape character must be character string of length 1

[frank.hoffsummer@…]

Hello,
In order to update my project to a later version of trunk, I recreated all tables in my DB (dropped all tables and sequences, and then performed a syncdb with the newer django version)
My Database is an Oracle 9i.

Strangely enough, now I get 500 server errors executing some of my views (that worked fine previously).
The problem is always:

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

I know, this sounds related to ticket 5558, but it isnt
I looked in the debug view, which query is executed when the problem arises, and it looks as follows:

args  	[':arg0', ':arg1', ':arg2']
i 	2
params 	('2007-10-21 00:00:00', '2007-11-19 16:08:17.025442', 'webb%')
query 	u'SELECT COUNT(*) FROM "LOG_JOBSTATUS" INNER JOIN "CONFIG_CHANNEL" "LOG_JOBSTATUS__CHANNEL" ON "LOG_JOBSTATUS"."CHANNEL_ID" = "LOG_JOBSTATUS__CHANNEL"."ID" WHERE ("LOG_JOBSTATUS"."STARTED" BETWEEN :arg0 AND :arg1 AND UPPER("LOG_JOBSTATUS__CHANNEL"."NAME") LIKE UPPER(:arg2) ESCAPE \'\\\')'
self 	<django.db.backends.oracle.base.FormatStylePlaceholderCursor on <cx_Oracle.Connection to mcca@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=arnd.svt.se)(PORT=1522)))(CONNECT_DATA=(SID=matd)))>>

Oracle's problem seems to be the ESCAPE \'
\' at the end of the sql query string.

I looked at oracle/base.py and saw that the original string is ESCAPE '
' . Looks like a bug in the escape handling to me (i.e. the quotes are escaped before the escaped backslash is un-escaped). Or something like that....

[frank.hoffsummer@…]

as a remedy, I have changed the file django/db/backends/oracle/base.py and changed the following

    operators = {
        'exact': '= %s',
        'iexact': '= UPPER(%s)',
        'contains': "LIKE %s ESCAPE '\\'",
        'icontains': "LIKE UPPER(%s) ESCAPE '\\'",
        'gt': '> %s',
        'gte': '>= %s',
        'lt': '< %s',
        'lte': '<= %s',
        'startswith': "LIKE %s ESCAPE '\\'",
        'endswith': "LIKE %s ESCAPE '\\'",
        'istartswith': "LIKE UPPER(%s) ESCAPE '\\'",
        'iendswith': "LIKE UPPER(%s) ESCAPE '\\'",
    }

to

    operators = {
        'exact': '= %s',
        'iexact': '= UPPER(%s)',
        'contains': "LIKE %s",
        'icontains': "LIKE UPPER(%s)",
        'gt': '> %s',
        'gte': '>= %s',
        'lt': '< %s',
        'lte': '<= %s',
        'startswith': "LIKE %s",
        'endswith': "LIKE %s",
        'istartswith': "LIKE UPPER(%s)",
        'iendswith': "LIKE UPPER(%s)",
    }

now count, icontains and all the others work....

[Erin Kelly]

The escape clause in the description looks normal to me; that's the repr of the query, which is why the extra escaping is showing up. After evaluating the escapes, you get ESCAPE '\', which is exactly what should be passed to the database.

The suggested change won't work in general. Django automatically escapes % and _ characters in filter strings, and without the ESCAPE clause, Oracle won't recognize them as escaped.

I think the problem is probably in some other part of the query. The one part of the query that strikes me as odd is the inner join:

"LOG_JOBSTATUS" INNER JOIN "CONFIG_CHANNEL" "LOG_JOBSTATUS__CHANNEL" ON "LOG_JOBSTATUS"."CHANNEL_ID" = "LOG_JOBSTATUS__CHANNEL"."ID"

Is it correct for "CONFIG_CHANNEL" "LOG_JOBSTATUS__CHANNEL" to be run together like that?

[anonymous]

ok, i really am not an oracle or SQL expert

but doesn't the fact that

• oracle complains specifically about the escape character beeing longer than 1

• everything works whene i remove the escape statement (which is no solution, i know)

indicate that something is wrong with the escape character?

as for the query, I fired up manage.py dbshell and pasted the raw sql query into the sqlplus prompt (using the clue you gave me about how the evaluated escape should look like)

SELECT COUNT(*) FROM "LOG_JOBSTATUS" INNER JOIN "CONFIG_CHANNEL" "LOG_JOBSTATUS__CHANNEL" ON "LOG_JOBSTATUS"."CHANNEL_ID" = "LOG_JOBSTATUS__CHANNEL"."ID" WHERE ("LOG_JOBSTATUS"."STARTED" BETWEEN '2007-19-11 00:00:00' AND '2007-19-12 00:00:00' AND UPPER("LOG_JOBSTATUS__CHANNEL"."NAME") LIKE UPPER('barnspelaren%') ESCAPE '\');

the result: "ORA-01425: escape character must be character string of length 1"

should my query contain that inner join? i am filtering rows from a table ("LOG_JOBSTATUS") for a certain attribute of a foreign key ("LOG_JOBSTATUSCHANNEL"."NAME").

"LOG_JOBSTATUSCHANNEL" is a foreign key to "CONFIG_CHANNEL"

bummer...

[Erin Kelly]

I've tried to reproduce this using Frank's models and query, but I was unable to get it to break. If anybody else runs into this problem, please let me know.

[philipp.jocham@…]

Hello, I've encountered exactly the same problem here. I'm using the django-trunk r7367.
As I see it, the definition of the escape clause is correct, but somewhere the string must get quoted, protecting the ' and the \, thus yielding

  ESCAPE \'\\\'. 

Which looks as a correct behaviour, if this characters appear in the searchstring. So could it be that
there's some code

  quote("LIKE %s ESCAPE '\\'" % value) 

instead of

  "LIKE %s ESCAPE '\\'" % quote(value) 

?

When I try to issue the sql command via manage.py dbshell I have to use

  ESCAPE '\\'

to work correctly, since sqlplus uses \ for escaping as well.

[Erin Kelly]

As I've already noted, the reason for the extra quoting is because what's displayed is the repr() of the query, as evidenced by the fact that it appears enclosed in u''. If the query already had extra quoting on top of that, we should see some awful thing like ESCAPE \\\'\\\\\\\', or at least ESCAPE \'\\\\\'.

You can disable the sqlplus escaping with the command SET ESCAPE OFF. But since cx_Oracle doesn't invoke sqlplus, I don't see how that can be the issue.

A coworker has suggested to me that this is probably a character set issue. What character set are you using? I can't test it here, so would you be willing to start a dbshell, try the following queries, and report back to me which of them work?

SET ESCAPE OFF
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\';
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\\';
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKEC 'foo\%bar' ESCAPE '\';
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE TO_CHAR('\');
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE TO_NCHAR('\');
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE ASCIISTR('\');

Thanks in advance!

[philipp.jocham@…]

No problem, I'm eager to help and solve this issue.

SQL> SET ESCAPE OFF;
SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\';

         1
----------
         1

SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\\';
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\\'
                                                             *
ERROR at line 1:
ORA-01425: escape character must be character string of length 1


SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKEC 'foo\%bar' ESCAPE '\';

         1
----------
         1

SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE TO_CHAR('\');

         1
----------
         1

SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE TO_NCHAR('\');
SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE TO_NCHAR('\')
                                                                    *
ERROR at line 1:
ORA-01425: escape character must be character string of length 1


SQL> SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE ASCIISTR('\');

         1
----------
         1

For the characterset

SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
WE8ISO8859P1

[Erin Kelly]

Oops, looks like in sqlplus with set escape off, the issue doesn't even appear. I should have asked you to try it through the oracle backend instead of through the dbshell. Would you please also try the following from a python manage.py shell? Hopefully, this run should reveal a solution.

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\'")
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\\'")
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKEC 'foo\%%bar' ESCAPE '\'")
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_CHAR('\')")
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_NCHAR('\')")
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE ASCIISTR('\')")

[anonymous]

Sorry for the delays, but I don't have access to the machine every day.

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\'")
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\\'")
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/home/jp012365/django/django/db/backends/util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "/home/jp012365/django/django/db/backends/oracle/base.py", line 499, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: ORA-01425: escape character must be character string of length 1

>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKEC 'foo\%%bar' ESCAPE '\'")
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_CHAR('\')")
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_NCHAR('\')")
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/home/jp012365/django/django/db/backends/util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "/home/jp012365/django/django/db/backends/oracle/base.py", line 499, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: ORA-01425: escape character must be character string of length 1

>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE ASCIISTR('\')")
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
>>>

Seeing that you specified the strings as raw, I have tried to change the operators in db.backend.oracle.base.py too.
Whereas

  'startswith': r"LIKE %s ESCAPE '\\'"

works, and

  'startswith': r"LIKE %s ESCAPE '\'"

throws ORA-04125.

Regards,
Philipp

[anonymous]

Replying to ikelly:

I executed these statements in the django shell and got the same result as philipp:

In [1]: from django.db import connection
In [2]: cursor = connection.cursor()
In [3]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\'")
Out[3]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
In [4]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\\'")
---------------------------------------------------------------------------
<class 'cx_Oracle.DatabaseError'>         Traceback (most recent call last)

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/mcc/<ipython console> in <module>()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     16         start = time()
     17         try:
---> 18             return self.cursor.execute(sql, params)
     19         finally:
     20             stop = time()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/oracle/base.py in execute(self, query, params)
    497         query = smart_str(query, self.charset) % tuple(args)
    498         self._guess_input_sizes([params])
--> 499         return Database.Cursor.execute(self, query, params)
    500 
    501     def executemany(self, query, params=None):
<class 'cx_Oracle.DatabaseError'>: ORA-01425: escape character must be character string of length 1
In [5]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKEC 'foo\%%bar' ESCAPE '\'")
Out[5]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
In [6]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_CHAR('\')")
Out[6]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
In [7]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_NCHAR('\')")
---------------------------------------------------------------------------
<class 'cx_Oracle.DatabaseError'>         Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/mcc/<ipython console> in <module>()
/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     16         start = time()
     17         try:
---> 18             return self.cursor.execute(sql, params)
     19         finally:
     20             stop = time()
/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/oracle/base.py in execute(self, query, params)
    497         query = smart_str(query, self.charset) % tuple(args)
    498         self._guess_input_sizes([params])
--> 499         return Database.Cursor.execute(self, query, params)
    500 
    501     def executemany(self, query, params=None):
<class 'cx_Oracle.DatabaseError'>: ORA-01425: escape character must be character string of length 1
In [8]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE ASCIISTR('\')")
Out[8]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

[Erin Kelly]

Well, now that's just bizarre. I still think this is a character set issue, but why would it work when calling cursor.execute directly and not work when called by a QuerySet? Unfortunately,
'startswith': r"LIKE %s ESCAPE '\\'"
isn't the solution, because that does throw an ORA-01425 when I try it.

To be thorough, would you please try changing django.db.backends.oracle.base to use LIKEC instead of LIKE, to see whether that will work in failing Django query itself? If those fail, would you try the following queries as well? I'm wondering if maybe it has something to do with passing the LIKE mask as a bind parameter but including the literal escape character in the query itself.

cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE '\'", ['%'])
cursor.execute("SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE %s", ['%', '\\'])

Thanks, Ian

[frank.hoffsummer@…]

Replying to ikelly:
Ian,
I changed the part in django.db.backends.oracle.base to use LIKEC instead of LIKE

    operators = {
        'exact': '= %s',
        'iexact': '= UPPER(%s)',
        'contains': "LIKEC %s ESCAPE '\\'",
        'icontains': "LIKEC UPPER(%s) ESCAPE '\\'",
        'gt': '> %s',
        'gte': '>= %s',
        'lt': '< %s',
        'lte': '<= %s',
        'startswith': "LIKEC %s ESCAPE '\\'",
        'endswith': "LIKEC %s ESCAPE '\\'",
        'istartswith': "LIKEC UPPER(%s) ESCAPE '\\'",
        'iendswith': "LIKEC UPPER(%s) ESCAPE '\\'",
    }

restarted the webserver, accessed my view - everything works as it should!

this is good news... right?

I then used manage.py shell and executed these commands

from django.db import connection
cursor = connection.cursor()
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\'")
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\\'")
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKEC 'foo\%%bar' ESCAPE '\'")
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_CHAR('\')")
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_NCHAR('\')")
cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE ASCIISTR('\')")
cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE '\'", ['%'])
cursor.execute("SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE %s", ['%', '\\'])

with the following result

In [5]: from django.db import connection

In [6]: cursor = connection.cursor()

In [7]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\'")
Out[7]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

In [8]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE '\\'")
---------------------------------------------------------------------------
<class 'cx_Oracle.DatabaseError'>         Traceback (most recent call last)

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/mcc/<ipython console> in <module>()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     16         start = time()
     17         try:
---> 18             return self.cursor.execute(sql, params)
     19         finally:
     20             stop = time()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/oracle/base.py in execute(self, query, params)
    497         query = smart_str(query, self.charset) % tuple(args)
    498         self._guess_input_sizes([params])
--> 499         return Database.Cursor.execute(self, query, params)
    500 
    501     def executemany(self, query, params=None):

<class 'cx_Oracle.DatabaseError'>: ORA-01425: escape character must be character string of length 1


In [9]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKEC 'foo\%%bar' ESCAPE '\'")
Out[9]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

In [10]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_CHAR('\')")
Out[10]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

In [11]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE TO_NCHAR('\')")
---------------------------------------------------------------------------
<class 'cx_Oracle.DatabaseError'>         Traceback (most recent call last)

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/mcc/<ipython console> in <module>()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     16         start = time()
     17         try:
---> 18             return self.cursor.execute(sql, params)
     19         finally:
     20             stop = time()

/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site-packages/django/db/backends/oracle/base.py in execute(self, query, params)
    497         query = smart_str(query, self.charset) % tuple(args)
    498         self._guess_input_sizes([params])
--> 499         return Database.Cursor.execute(self, query, params)
    500 
    501     def executemany(self, query, params=None):

<class 'cx_Oracle.DatabaseError'>: ORA-01425: escape character must be character string of length 1


In [12]: cursor.execute(r"SELECT 1 FROM DUAL WHERE 'foo%%bar' LIKE 'foo\%%bar' ESCAPE ASCIISTR('\')")
Out[12]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

In [13]: cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE '\'", ['%'])
Out[13]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

In [14]: cursor.execute("SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE %s", ['%', '\\'])
Out[14]: [<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

hope this helps!

[anonymous]

Same here as with Frank, using LIKEC works

~/djangoserver$ python manage.py shell
Python 2.3.4 (#1, Feb  2 2005, 11:44:49)
[GCC 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from dbtest.models import *
>>> DBTest.objects.all()
[<DBTest: The Cheese Test>, <DBTest: The Parrot Test>, <DBTest: The Fish Slapping Test>]
>>> DBTest.objects.filter( name__contains = 'Parrot' )
[<DBTest: The Parrot Test>]

DBTest is a simple class with only one 'name' attribute.

And here is the output your two sql queries.

>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE '\'", ['%'])
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]
>>> cursor.execute("SELECT 1 FROM DUAL WHERE DUMMY LIKE %s ESCAPE %s", ['%', '\\'])
[<cx_Oracle.NUMBER with value [None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None]>]

It never came up, but for completness: I'm using a self-compiled cx_Oracle 4.3.3 and python 2.3.4.

I now the python version is old, but I can't change it that easily.

Philipp

[Erin Kelly]

Fixed in [7412]