ORA-01425

[Tim Sawyer]

Hello,

I'm using Django against an Oracle 10 database. It used to work. Then
they upgraded it to 10.2.0.5 (not sure what from, I can probably find out).

This query now gives: ORA-01425: escape character must be character
string of length 1.

'SELECT "BROKER_LENDER_LOG"."BLL_SECURITY_TOKEN",
"BROKER_LENDER_LOG"."BLL_TRANSACTION",
"BROKER_LENDER_LOG"."BLL_TYPE",
"BROKER_LENDER_LOG"."BLL_AGREEMENT_NUMBER",
"BROKER_LENDER_LOG"."BLL_MODULE",
"BROKER_LENDER_LOG"."BLL_LENDER",
"BROKER_LENDER_LOG"."BLL_SERIAL",
"BROKER_LENDER_LOG"."BLL_VERSION",
"BROKER_LENDER_LOG"."BLL_JOBID",
"BROKER_LENDER_LOG"."BLL_PROJECT",
"BROKER_LENDER_LOG"."BLL_XML",
"BROKER_LENDER_LOG"."BLL_LENDER_RESPONSE", "BROKER_LENDER_LOG"."BLL_DATE"
FROM "BROKER_LENDER_LOG" WHERE ("BROKER_LENDER_LOG"."BLL_JOBID" LIKE
TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE(\'\\\' USING NCHAR_CS) AND
"BROKER_LENDER_LOG"."BLL_MODULE" = %s ) ORDER BY
"BROKER_LENDER_LOG"."BLL_SERIAL" ASC'

I guess the problem is:

LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE(\'\\\' USING NCHAR_CS)

where it thinks that \'\\\' is more than one character?

Cheers for any clues,

Tim.

[Jirka Vejrazka]

> I'm using Django against an Oracle 10 database.  It used to work.  Then they
> upgraded it to 10.2.0.5 (not sure what from, I can probably find out).

Hi Tim,

sorry, I don't have a solution for you, but you might want to check
out http://code.djangoproject.com/ticket/14149 and add description of
your environment there so there is more "use cases" listed there.

Actually, I do have a solution - I do monkeypatch the Oracle backend
so it matches my version of Oracle (i.e. 2 or 4 backslashes).

Cheers

Jirka

P.S. This got me thinking - maybe the Oracle backend could do some
"autodetection" of which version works fine in the current
environment? Yes, it'd be extra query at startup I guess, but don't
really see any other way...

[Tim Sawyer]

Interesting. I'm using Django 1.2.3.

I just manually applied the diff from

http://code.djangoproject.com/ticket/5985

which is to change from LIKE to LIKEC and it works now. Has this
regressed for some reason?

Looks like it was
http://code.djangoproject.com/browser/django/trunk/django/db/backends/oracle/base.py?rev=12293
that regressed back to LIKE. http://code.djangoproject.com/ticket/11017
the reason.

Tim.

[Tim Sawyer]

Thanks for that.

Interesting that I'm seeing this in an upgrade from 10.2.0.4 to 10.2.0.5.

Instead of changing from LIKE to LIKEC - if I add more backslashes will
that work?

Cheers,

Tim.

[Jirka Vejrazka]

> Interesting that I'm seeing this in an upgrade from 10.2.0.4 to 10.2.0.5.
>
> Instead of changing from LIKE to LIKEC - if I add more backslashes will that
> work?

Honestly, I can't tell. It's been too long and I can't test it now :-(((

Jirka

[Ian]

On Dec 2, 8:40 am, Jirka Vejrazka <jirka.vejra...@gmail.com> wrote:

> P.S. This got me thinking - maybe the Oracle backend could do some
> "autodetection" of which version works fine in the current
> environment? Yes, it'd be extra query at startup I guess, but don't
> really see any other way...

It looks as though something like that may be necessary. For those of
you running into this problem, do you get the error with the following
query?

>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE TRANSLATE('A' USING NCHAR_CS) LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

I'd like to know whether this will work for autodetection.

Thanks,
Ian

[Jirka Vejrazka]

> It looks as though something like that may be necessary.  For those of
> you running into this problem, do you get the error with the following
> query?
>
>>>> cursor.execute(r"SELECT 1 FROM DUAL WHERE TRANSLATE('A' USING NCHAR_CS) LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

No error for me, but that's on a monkeypatched system. I can try
without the patch, but only after weekend :(

In [7]: res = c.execute(r"SELECT 1 FROM DUAL WHERE TRANSLATE('A' USING

NCHAR_CS) LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\'
USING NCHAR_CS)")

In [8]: list(res)
Out[8]: [(u'1',)]

Jirka

[Jirka Vejrazka]

> No error for me, but that's on a monkeypatched system. I can try
> without the patch, but only after weekend :(

Actually, screw production servers :) Tried on unpatched Django
1.2.1, got exactly the same result, no error.

Jirka

[Tim Sawyer]

I unpatched mine (changed LIKEC to LIKE) and then ran:

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> result = cursor.execute(r"SELECT 1 FROM DUAL WHERE TRANSLATE('A'

USING NCHAR_CS) LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\'
USING NCHAR_CS)")

>>> rows = cursor.fetchall()
>>> for row in rows:
> ... print row[0]
> ...
> 1
>>> print rows
> ((1,),)

Tim.

[Jani Tiainen]

I'm running against 10.2.0.5 (64 bit) and I don't see that problem appear.
Same appliest to other 10.2.0.x series, all of them work with that LIKE part.

Reason just was that LIKEC queries took ages in my modest 21M rows containing
address table.

Could that be some artifacts from upgrade migration progress - I know it has
happened sometimes.

Can you also post you charsets you're using, maybe they're causing some
oddities here - with Oracle you never know .

--

Jani Tiainen

[Tim Sawyer]

Hi,

We're on Solaris Sparc, 64-bit. Charset is WE8MSWIN1252

Tim.

[Ian]

On Dec 2, 3:17 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:
> I unpatched mine (changed LIKEC to LIKE) and then ran:
>
>  >>> from django.db import connection
>  >>> cursor = connection.cursor()
>  >>> result = cursor.execute(r"SELECT 1 FROM DUAL WHERE TRANSLATE('A'
> USING NCHAR_CS) LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\'
> USING NCHAR_CS)")
>  >>> rows = cursor.fetchall()
>  >>> for row in rows:
>  > ...   print row[0]
>  > ...
>  > 1
>  >>> print rows
>  > ((1,),)

Nuts. What if you remove the first TRANSLATE call, i.e.

cursor.execute(r"SELECT 1 FROM DUAL WHERE 'A' LIKE TRANSLATE('A' USING

NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

If that also doesn't trigger the error, then maybe we need an actual
column in the mix. Please try this also:

cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY LIKE TRANSLATE('X'

USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

Thanks,
Ian

[Tim Sawyer]

Oracle is very weird sometimes.

>>> from django.db import connection
>>> cursor = connection.cursor()

>>> result = cursor.execute(r"SELECT 1 FROM DUAL WHERE 'A'

LIKE TRANSLATE('A' USING NCHAR_CS) ESCAPE TRANSLATE('\' USING
NCHAR_CS)")

>>> rows = cursor.fetchall()
>>> for row in rows:
... print row[0]
...
1
>>> print rows
((1,),)

>>> result = cursor.execute(r"SELECT 1 FROM DUAL WHERE DUMMY

LIKE TRANSLATE('X' USING NCHAR_CS) ESCAPE TRANSLATE('\' USING
NCHAR_CS)")

Traceback (most recent call last):
File "<console>", line 1, in<module>
File
"/dev/HEAD/BESPOKE/app/websites/app\django\db\backends\util.py", line
15, in execute
return self.cursor.execute(sql, params)
File
"/dev/HEAD/BESPOKE/app/websites/app\django\db\backends\oracle\base.py",
line 507, in execute
return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-01425: escape character must be character string of
length 1

Tim.

[Jirka Vejrazka]

OK - here's my 2 cents:

Django 1.2.1, Oracle 9.2.0.7

Cheers

Jirka

In [1]: from django.db import connections

In [2]: c = connections['oracle'].cursor()

In [3]: c.execute(r"SELECT 1 FROM DUAL WHERE 'A' LIKE TRANSLATE('A'

USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

---------------------------------------------------------------------------
<class 'django.db.utils.DatabaseError'> Traceback (most recent call last)

/data/ig/webapps/ig/<ipython console> in <module>()

/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py in
execute(self, query, params)
505 self._guess_input_sizes([params])
506 try:
--> 507 return self.cursor.execute(query,
self._param_generator(params))
508 except Database.IntegrityError, e:
509 raise utils.IntegrityError,
utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

<class 'django.db.utils.DatabaseError'>: ORA-01425: escape character

must be character string of length 1

In [4]: c.execute("SELECT 1 FROM DUAL WHERE DUMMY LIKE TRANSLATE('X'

USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)")

---------------------------------------------------------------------------
<class 'django.db.utils.DatabaseError'> Traceback (most recent call last)

/data/ig/webapps/ig/<ipython console> in <module>()

/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py in
execute(self, query, params)
505 self._guess_input_sizes([params])
506 try:
--> 507 return self.cursor.execute(query,
self._param_generator(params))
508 except Database.IntegrityError, e:
509 raise utils.IntegrityError,
utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

<class 'django.db.utils.DatabaseError'>: ORA-01425: escape character

[Jani Tiainen]

Can you post your database NLS parameters, version of cx_oracle and is it ANSI
or UNICODE enabled.

Since I've run tests on both 9.2.0.5, 9.2.0.7 and various versions on 10g/11g
and XE also I run Django all the time on production on Oracle.

I've never experienced such a problem so it must be something else that's
causing this unfortunate side-effect.

--

Jani Tiainen