Re: [cx-oracle-users] Invalid Number error when updating a NUMBER field using parameter subsitition

[Wong W. Meng-R. <r3...@fr...>]

Wow, you are right. In that manner my existing codes where I replicate the use of the same value also will not work as the number of parameter requiring value and the number of element in the sequence must be tally now.

My team is migrating application from python 1.5.2 to python 2.7.1, from oracledb to cx_Oracle, looks like this requires a big effort to revamp the coding.

Anyway, thanks a lot for pointing out the root cause of my issue. :)

Regards,
Wah Meng

________________________________
From: Chris Gould [mailto:chr...@to...]
Sent: Thursday, October 13, 2011 2:05 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Invalid Number error when updating a NUMBER field using parameter subsitition

I'd suggest it's because the association of bind-variable to value in the array is done positionally, not numerically.
That is, in your statement the first bind variable encountered in the statement is :2, so that's associated with the first value in the array ('ftcs'). Hence the problem.

On 13 October 2011 06:42, Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>> wrote:
Hello guys,

Can anyone advise why am I hitting invalid number error when trying to update a NUMBER field using parameters?

The first SQL update works, however, not the second one. Is there a different way I need to pass to cx_Oracle if the parameter were a NUMBER field?


SQL 1 (successful)
>>> a.execute("update USERS set USER_LOGIN_FAILURE_ATTEMPT = 1 where USER_ID = 'ftcs'")

SQL 2 (failed)
>>> a.execute("update USERS set USER_LOGIN_FAILURE_ATTEMPT = :2 where USER_ID = :1", ('ftcs', 1))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line 821, in __call__
    self._method, args, kw )
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1779, in _genericInvocation
    reply = self._requestReply( replyBit, (method_name, args, kw) )
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1612, in _requestReply
    reply = self._postReceive(reply)
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1748, in _postReceive
    raise ValueError(post_msg[1])  # raise application-level exception
ValueError: ORA-01722: invalid number


 USER_LAST_PW4_CHANGE                               DATE
 USER_OLD_PW5                                       VARCHAR2(30)
 USER_LAST_PW5_CHANGE                               DATE
 USER_LOGIN_FAILURE_ATTEMPT                         NUMBER
 REASON                                             VARCHAR2(64)
 USER_LOGIN_FAILURE_REASON                          VARCHAR2(64)
 USER_LAST_LOGIN_DATE                               DATE
 USER_HR_DEPT                                       VARCHAR2(10)

Regards,
Wah Meng


------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2d-oct
_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users