Re: [cx-oracle-users] Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion requested
|
From: Anurag C. <anu...@gm...> - 2013-04-04 02:36:19
|
So the problem is only with named parameters? Or is there any other
alternate recommended way? Does cx_Oracle support positional parameters?
Shall I continue using the following way of writing queries then?
cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR =
'%s' " %(TAG_NBR))
In any case I will give this a shot with 64 Bit version of cx_Oracle
tomorrow but I am not sure of that will make any difference.
Appreciate your feedback.
Regards,
Anurag
On Wed, Apr 3, 2013 at 7:41 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:
> Hi,
>
> This thread: https://forums.oracle.com/forums/thread.jspa?threadID=507725
> Suggests a bug in Oracle 10.2.0.1.0...
>
>
> 2013/4/4 Anurag Chourasia <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
>>>
>>>
>>
>
>
> --
> Amaury Forgeot d'Arc
>
|
