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

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

Type of TAG_NBR in Python is <type 'str'>
TAG_HIST.TAG_NBR column is varchar2 (100)

In this particular example, the value of TAG_NBR was 'ABC'

Regards,
Anurag

On Wed, Apr 3, 2013 at 6:48 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> Hi,
>
> 2013/4/3 Anurag Chourasia <anu...@gm...>
>
>> Hi Joel,
>>
>> Yes. It is a 64 Bit Server using 32 Bit version of cx_Oracle.....Could
>> that be a problem? Given that on the same server
>>
>> This 'Works Fine'
>>
>> cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = '%s' " %(TAG_NBR))
>>
>> ......but this 'Does Not'
>>
>> cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})
>>
>>
> What is the type of the TAG_NBR variable in Python?
> And what is the type of the TAG_HIST.TAG_NBR column?
>
>
>>  Regards,
>> Anurag
>>
>> On Wed, Apr 3, 2013 at 4:50 PM, Joel Slowik <js...@cp...> wrote:
>>
>>>  64-bit server but using 32-bit cx_oracle?
>>>
>>>
>>>
>>> *From:* Anurag Chourasia [mailto:anu...@gm...]
>>> *Sent:* Wednesday, April 03, 2013 4:46 PM
>>> *To:* cx-...@li...; re...@gm...
>>> *Subject:* [cx-oracle-users] Named Parameters Issue - ORA-01460:
>>> unimplemented or unreasonable conversion requested
>>>
>>>
>>>
>>> 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
>>>
>>>
>>>
>>>
>>>  ------------------------------
>>> Confidentiality Note: This electronic message transmission is intended
>>> only for the person or entity to which it is addressed and may contain
>>> information that is privileged, confidential or otherwise protected from
>>> disclosure. If you have received this transmission, but are not the
>>> intended recipient, you are hereby notified that any disclosure, copying,
>>> distribution or use of the contents of this information is strictly
>>> prohibited. If you have received this e-mail in error, please contact
>>> Continuum Performance Systems at {203.245.5000} and delete and destroy
>>> the original message and all copies.
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Minimize network downtime and maximize team effectiveness.
>>> Reduce network management and security costs.Learn how to hire
>>> the most talented Cisco Certified professionals. Visit the
>>> Employer Resources Portal
>>> http://www.cisco.com/web/learning/employer_resources/index.html
>>>
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Minimize network downtime and maximize team effectiveness.
>> Reduce network management and security costs.Learn how to hire
>> the most talented Cisco Certified professionals. Visit the
>> Employer Resources Portal
>> http://www.cisco.com/web/learning/employer_resources/index.html
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> --
> Amaury Forgeot d'Arc
>
>
> ------------------------------------------------------------------------------
> Minimize network downtime and maximize team effectiveness.
> Reduce network management and security costs.Learn how to hire
> the most talented Cisco Certified professionals. Visit the
> Employer Resources Portal
> http://www.cisco.com/web/learning/employer_resources/index.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>