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-05 18:43:40
|
Dear All...I encountered another behavior today.
This issue presents itself only when the cx_Oracle code is run inside the
Web App (Hosted on Apache).
If i run the same code with named parameters from within the python command
line then the query runs just fine.
Appreciate if someone could provide me some ideas.
Regards,
Anurag
On Wed, Apr 3, 2013 at 10:35 PM, Anurag Chourasia <
anu...@gm...> wrote:
> 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
>>
>
>
|
