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

Posting this one more time to see if someone has more ideas.

I am finally sticking to using the queries of this sort

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

I know that this is not the best way of writing queries but I am out of
options at the moment with the implementation date nearing by.

Thanks for your help and guidance.

Regards,
Anurag

On Fri, Apr 5, 2013 at 3:00 PM, Anurag Chourasia <[email protected]
> wrote:

> 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