Re: [cx-oracle-users] cx-oracle-users Digest, Vol 81, Issue 9

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

PLEASE print out the type of TAG_NBR!
print 'TAG_NBR:', repr(TAG_NBR), type(TAG_NBR)

And watch out for NLS_ environment variables!

2013/4/5 <cx-...@li...>

> Send cx-oracle-users mailing list submissions to
>         cx-...@li...
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> or, via email, send a message with subject or body 'help' to
>         cx-...@li...
>
> You can reach the person managing the list at
>         cx-...@li...
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of cx-oracle-users digest..."
>
>
> Today's Topics:
>
>    1. Re: Named Parameters Issue - ORA-01460: unimplemented or
>       unreasonable conversion requested (Anurag Chourasia)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 5 Apr 2013 15:00:39 -0400
> From: Anurag Chourasia <anu...@gm...>
> Subject: Re: [cx-oracle-users] Named Parameters Issue - ORA-01460:
>         unimplemented or unreasonable conversion requested
> To: cx-...@li..., iva...@gm...
> Message-ID:
>         <CANFgmFBj7Z=KBzmKJoi_0A1v5=
> Da-...@ma...>
> Content-Type: text/plain; charset="iso-8859-1"
>
> 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 <iva...@gm...
> >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 <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
> >>
> >>
> >
> >
> >
> ------------------------------------------------------------------------------
> > 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
> >
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
>
> ------------------------------------------------------------------------------
> 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
>
>
> End of cx-oracle-users Digest, Vol 81, Issue 9
> **********************************************
>