Re: Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion requested

Hi Ivanelson,

I am on Windows Server 2003. My original post has all the details.

Basically the following code "Works" on my clients machine from both within
Apache and Python Command line

    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
TAG_NBR = '%s' " %(TAG_NBR))



But the following code "Does Not Work" from Within Apache but Works from
Python Command line

    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})

Regards,
Anurag

On Fri, Apr 5, 2013 at 2:55 PM, Ivanelson Nunes <[email protected]>wrote:

> 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 <[email protected]>
>
>> 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 <
>> [email protected]> 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 <[email protected]
>>> > 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 <[email protected]>
>>>>
>>>>> 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 <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> 2013/4/3 Anurag Chourasia <[email protected]>
>>>>>>
>>>>>>> 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 <[email protected]>wrote:
>>>>>>>
>>>>>>>>  64-bit server but using 32-bit cx_oracle?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> *From:* Anurag Chourasia [mailto:[email protected]]
>>>>>>>> *Sent:* Wednesday, April 03, 2013 4:46 PM
>>>>>>>> *To:* cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org; [email protected]
>>>>>>>> *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 <
>>>>>>>> [email protected]> wrote:
>>>>>>>>
>>>>>>>> Thanks Jani...With that my issue is solved.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Regards
>>>>>>>>
>>>>>>>> Guddu
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Tue, Apr 2, 2013 at 1:35 AM, Jani Tiainen <[email protected]>
>>>>>>>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>>>>>>>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>>>>>>>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>>>>>>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>>>>>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users