[cx-oracle-users] Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion requested

[Anurag C. <anu...@gm...>]

I have encountered a problem after implementing these changes (named
parameters in RAW SQL Queries as per Python DB-API) and I am without any
clue at the moment.

These changes worked just fine on my Development Server

   - Windows XP
   - Oracle XE SQL*Plus: Release 11.2.0.2.0
   - cx_Oracle-5.1.2-11g.win32-py2.7


However, when deployed on my Client's Test Server, execution of all queries
fail with this error.

   - Windows Server 2003
   - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
   - cx_Oracle-5.1.2-10g.win32-py2.7


Traceback (most recent call last):
  File "C:/Program Files/App_Logic/..\apps\views.py", line 400, in
regularize_TAG
    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})
DatabaseError: ORA-01460: unimplemented or unreasonable conversion requested

Basically the following code "Works" on my clients machine
    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
TAG_NBR = '%s' " %(TAG_NBR))

But the following code "Does Not Work"
    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})

Appreciate if someone could help me through this issue.

Regards,
Anurag


On Tue, Apr 2, 2013 at 5:38 PM, Anurag Chourasia <anu...@gm...
> wrote:

> Thanks Jani...With that my issue is solved.
>
> Regards
> Guddu
>
>
> On Tue, Apr 2, 2013 at 1:35 AM, Jani Tiainen <re...@gm...> wrote:
>
>> 2.4.2013 5:51, Anurag Chourasia kirjoitti:
>> > *Dear friends...Could you please help me with this issue?*
>> >
>> > *Steps to reproduce?*
>> > 1. Fetch the value of a varchar2 column that has Null as its current
>> value.
>> > 2. Use the fetched value to update any table's column.
>> >
>> > *Expected output?*
>> > Instead of updating to Null, the table column gets updated to a string
>> value of 'None'
>> >
>> > *Version in use*
>> > Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit
>> (Intel)] on win32
>> > cx_Oracle - 5.1.2
>> > Oracle 11G
>> >
>> > *Additional information*
>> >
>> > This could be because in the update query, I am using '%s'
>> format....for example
>> >
>> > cursor.execute("UPDATE EMP SET NAME='%s'" %(None))
>> >
>> > But there is no other way. If i simply use
>> >
>> > cursor.execute("UPDATE EMP SET NAME=%s" %(None)) then I get a syntax
>> error.
>> >
>> > There should be a way to update a column to NULL
>>
>> Why are you trying to inject data instead of using standard Python API
>> to do things right?
>>
>> If you persists constructing query string completely by hand (like you
>> have done), then you have to take care of NULL by yourself.
>>
>> In most cases following works better:
>>
>> cursor.execute('UPDATE EMP SET NAME=:arg1', {'arg1' : None })
>>
>> When you do it that way Python DB-API takes care of correct quotation
>> and makes sure that there is no way to inject malicious data and in most
>> cases correct datatype conversion takes a place (there is few occassions
>> when this fails)
>>
>> cx_Oracle doesn't support %s format, it supports only :name (named)
>> format [1].
>>
>> [1] http://www.python.org/dev/peps/pep-0249/#paramstyle
>> --
>> Jani Tiainen
>>
>> - Well planned is half done and a half done has been sufficient before...
>>
>>
>> ------------------------------------------------------------------------------
>> Own the Future-Intel(R) Level Up Game Demo Contest 2013
>> Rise to greatness in Intel's independent game demo contest. Compete
>> for recognition, cash, and the chance to get your game on Steam.
>> $5K grand prize plus 10 genre and skill prizes. Submit your demo
>> by 6/6/13. http://altfarm.mediaplex.com/ad/ck/12124-176961-30367-2
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>