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: Amaury F. d'A. <ama...@gm...> - 2013-04-03 23:41:17
|
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 |
