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: Ivanelson N. <iva...@gm...> - 2013-04-05 18:56:07
|
Check the environment variables. I set up in [1] /etc/apache2/envvars
Example my file envvars:
## Settings My Oracle
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
[1] Ubuntu
2013/4/5 Anurag Chourasia <anu...@gm...>
> 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
>>>
>>
>>
>
>
> ------------------------------------------------------------------------------
> 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
>
>
|
