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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

As I understand it, whatever version of cx_oracle you have should have been compiled for whatever version of oracle you are using. I just tested a variant of your code that "does not work" on my system and it returned a value. Short of you using an incompatible version there are a few other reasons why you would get ORA-01460 but I would start there.

From: Anurag Chourasia [mailto:anu...@gm...]
Sent: Wednesday, April 03, 2013 4:56 PM
To: cx-...@li...; Joel Slowik
Subject: Re: [cx-oracle-users] Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion requested

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})
Regards,
Anurag
On Wed, Apr 3, 2013 at 4:50 PM, Joel Slowik <js...@cp...<mailto:js...@cp...>> wrote:
64-bit server but using 32-bit cx_oracle?

From: Anurag Chourasia [mailto:anu...@gm...<mailto:anu...@gm...>]
Sent: Wednesday, April 03, 2013 4:46 PM
To: cx-...@li...<mailto:cx-...@li...>; re...@gm...<mailto: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...<mailto: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...<mailto: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...<mailto: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<tel: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...<mailto: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.