Thread: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-11-30 11:23:22
|
Hello there,
I am continuing on my python 1.5.2 to 2.7.1, oracledb to cx_Oracle conversion project.
My application server is coded with following SQL statements and arguments in one of the API. My NLS_LANG setting caused the error description not displayable in English but the description has been stated in the subject of this email.
By design of the applicaiton, the ATTR_VALUE column can store value in python list format. I will encounter the same error no matter the value is an empty list or a list with elements. Is there a workaround to this in cx_Oracle, like overriding the inputtypehandler for the cursor object? The code used to be working fine in oracledb.
Regards,
Wah Meng
>> cmd="insert into MES_ATTRIBUTE_HISTORIES (ENTITY_TYPE, ENTITY_ID, ATTR_NAME, SET_TIME, SET_USER_ID, ATTR_VALUE, COMMENTS, RCDE_ID) values ('ALOT', :1, :2, :3, :4, :5, :6, :7)"
>> arg=('TJMEA12KAH00', 'wipRack', (2011, 11, 25, 9, 55, 30), 'R33088', [], None, None)
>>> a.execute(cmd, arg)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line 896, in __call__
raiseAppExc(sys.exc_info()[0].__name__, sys.exc_info()[1].__str__())
File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line 191, in raiseAppExc
raise excClass(value)
EComponent.DatabaseError: ORA-01484: ?????? PL/SQL ????
Regards,
Wah Meng
|
|
From: Amaury F. d'A. <ama...@gm...> - 2011-11-30 11:29:01
|
2011/11/30 Wong Wah Meng-R32813 <r3...@fr...> > By design of the applicaiton, the ATTR_VALUE column can store value in > python list format. But how is it stored in the database? "python list format" is not close to any valid SQL column type. -- Amaury Forgeot d'Arc |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-11-30 12:27:03
|
The field is a VARCHAR2 column. SQL> desc MES_ATTRIBUTE_HISTORIES; Name Null? Type ----------------------------------------- -------- ---------------------------- ENTITY_TYPE VARCHAR2(4) ENTITY_ID VARCHAR2(100) ATTR_NAME VARCHAR2(32) SET_TIME DATE SET_USER_ID VARCHAR2(32) ATTR_VALUE VARCHAR2(2000) COMMENTS VARCHAR2(255) RCDE_ID VARCHAR2(8) The data we store can be either an empty list, or some values. SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack'; ........ ........ ........ ....... KTMTEST:KT06 [] [] KTMPQCIL:KPQ03 [] KTMQA:KQ01 [] [] [] [] KTMTEST:KT06 [] [] KTMPQCIL:KPQ03 [] KTMQA:KQ02 [] KTMQA:KQ01 [] [] [] [] [] TSGTEST:359 [] [] [] [] [] [] ^C [] 8885 rows selected. Regards, Wah Meng ________________________________ From: Amaury Forgeot d'Arc [mailto:ama...@gm...] Sent: Wednesday, November 30, 2011 7:29 PM To: cx-...@li... Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>> By design of the applicaiton, the ATTR_VALUE column can store value in python list format. But how is it stored in the database? "python list format" is not close to any valid SQL column type. -- Amaury Forgeot d'Arc |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-11-30 12:39:46
|
My application also has another usage of passing list items as parameters though the column STEP_NAME is not meant to store data as list item. >> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in (:1) and LOT_ID = :2 and ......." >> arg = (['HIGH TEST-DTS'], 'XXXXX',.....) >> execute(cmd, arg) >> "<type 'exceptions.ValueError'>", 'ORA-01484: arrays can only be bound to PL/SQL statements\n') Regards, Wah Meng ________________________________ From: Wong Wah Meng-R32813 Sent: Wednesday, November 30, 2011 8:27 PM To: 'cx-...@li...' Subject: RE: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements The field is a VARCHAR2 column. SQL> desc MES_ATTRIBUTE_HISTORIES; Name Null? Type ----------------------------------------- -------- ---------------------------- ENTITY_TYPE VARCHAR2(4) ENTITY_ID VARCHAR2(100) ATTR_NAME VARCHAR2(32) SET_TIME DATE SET_USER_ID VARCHAR2(32) ATTR_VALUE VARCHAR2(2000) COMMENTS VARCHAR2(255) RCDE_ID VARCHAR2(8) The data we store can be either an empty list, or some values. SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack'; ........ ........ ........ ....... KTMTEST:KT06 [] [] KTMPQCIL:KPQ03 [] KTMQA:KQ01 [] [] [] [] KTMTEST:KT06 [] [] KTMPQCIL:KPQ03 [] KTMQA:KQ02 [] KTMQA:KQ01 [] [] [] [] [] TSGTEST:359 [] [] [] [] [] [] ^C [] 8885 rows selected. Regards, Wah Meng ________________________________ From: Amaury Forgeot d'Arc [mailto:ama...@gm...] Sent: Wednesday, November 30, 2011 7:29 PM To: cx-...@li... Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>> By design of the applicaiton, the ATTR_VALUE column can store value in python list format. But how is it stored in the database? "python list format" is not close to any valid SQL column type. -- Amaury Forgeot d'Arc |
|
From: Amaury F. d'A. <ama...@gm...> - 2011-11-30 13:06:07
|
2011/11/30 Wong Wah Meng-R32813 <r3...@fr...> > >> cmd = “select …….. from FUTURE_HOLD_CONDITIONS where STEP_NAME not in > (:1) and LOT_ID = :2 and …….” This cannot work with Oracle. A placeholder can only replace one value. (Think of the IN operator as a sequence of OR conditions) Yes, this is a long standing issue with Oracle. I suggest to try with %s formatting. -- Amaury Forgeot d'Arc |
|
From: Amaury F. d'A. <ama...@gm...> - 2011-11-30 13:08:35
|
2011/11/30 Wong Wah Meng-R32813 <r3...@fr...> > SQL> Select attr_value from mes_attribute_histories where > entity_type='ALOT' and attr_name='wipRack’;**** > > ** ** > > ……..**** > > ……..**** > > ……..**** > > …….**** > > KTMTEST:KT06**** > > []**** > > []**** > > KTMPQCIL:KPQ03**** > > []**** > > KTMQA:KQ01**** > > []**** > > []**** > > []**** > > ** > So, use str() and your list will be converted into a string :-) -- Amaury Forgeot d'Arc |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-12-01 02:06:29
|
Yes I thought of this. I will convert the empty list [] as string and store it into the column. :) Regards, Wah Meng ________________________________ From: Amaury Forgeot d'Arc [mailto:ama...@gm...] Sent: Wednesday, November 30, 2011 9:08 PM To: cx-...@li... Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>> SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack'; ........ ........ ........ ....... KTMTEST:KT06 [] [] KTMPQCIL:KPQ03 [] KTMQA:KQ01 [] [] [] So, use str() and your list will be converted into a string :-) -- Amaury Forgeot d'Arc |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-12-01 02:07:25
|
Yes worst case is we will have to modify this SQL statement. However, is it a long standing issue with Oracle or cx_Oracle? This is an old code and the passing of parameters in such way has been working fine on oracledb. So is this something the cx_Oracle does not support? IF it is oracle issue I wonder whey it did not give the error when we use oracledb. Regards, Wah Meng ________________________________ From: Amaury Forgeot d'Arc [mailto:ama...@gm...] Sent: Wednesday, November 30, 2011 9:06 PM To: cx-...@li... Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>> >> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in (:1) and LOT_ID = :2 and ......." This cannot work with Oracle. A placeholder can only replace one value. (Think of the IN operator as a sequence of OR conditions) Yes, this is a long standing issue with Oracle. I suggest to try with %s formatting. -- Amaury Forgeot d'Arc |
|
From: Anthony T. <ant...@gm...> - 2011-12-01 04:28:57
|
Oracle does not allow passing arrays to SQL statements, only PL/SQL statements and this has been that way since as long as I have known Oracle. The driver you were using before may have simply converted the result to a string. cx_Oracle takes lists and converts them to PL/SQL arrays -- which are not possible to be used in straight SQL statements. So you will have to modify how you are using the driver. In my opinion, converting lists to strings in some arbitrary fashion is not the best way to handle such things! Anthony On Wed, Nov 30, 2011 at 7:07 PM, Wong Wah Meng-R32813 <r3...@fr...> wrote: > Yes worst case is we will have to modify this SQL statement. > > > > However, is it a long standing issue with Oracle or cx_Oracle? This is an > old code and the passing of parameters in such way has been working fine on > oracledb. So is this something the cx_Oracle does not support? IF it is > oracle issue I wonder whey it did not give the error when we use oracledb. > > > > Regards, > > Wah Meng > > ________________________________ > > From: Amaury Forgeot d'Arc [mailto:ama...@gm...] > Sent: Wednesday, November 30, 2011 9:06 PM > > To: cx-...@li... > Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be > bound to PL/SQL statements > > > > 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...> > >>> cmd = “select …….. from FUTURE_HOLD_CONDITIONS where STEP_NAME not in >>> (:1) and LOT_ID = :2 and …….” > > This cannot work with Oracle. A placeholder can only replace one value. > > (Think of the IN operator as a sequence of OR conditions) > > Yes, this is a long standing issue with Oracle. > > I suggest to try with %s formatting. > > > > -- > Amaury Forgeot d'Arc > > ------------------------------------------------------------------------------ > 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. IT sense. And common sense. > http://p.sf.net/sfu/splunk-novd2d > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-12-01 05:13:43
|
Hi Anthony, Thanks, and agreed with you on simply converting lists to string in arbitrary fashion is not the best way to handle this. I will feedback this to the application development team. Thanks a lot! Regards, Wah Meng -----Original Message----- From: Anthony Tuininga [mailto:ant...@gm...] Sent: Thursday, December 01, 2011 12:29 PM To: cx-...@li... Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements Oracle does not allow passing arrays to SQL statements, only PL/SQL statements and this has been that way since as long as I have known Oracle. The driver you were using before may have simply converted the result to a string. cx_Oracle takes lists and converts them to PL/SQL arrays -- which are not possible to be used in straight SQL statements. So you will have to modify how you are using the driver. In my opinion, converting lists to strings in some arbitrary fashion is not the best way to handle such things! Anthony On Wed, Nov 30, 2011 at 7:07 PM, Wong Wah Meng-R32813 <r3...@fr...> wrote: > Yes worst case is we will have to modify this SQL statement. > > > > However, is it a long standing issue with Oracle or cx_Oracle? This is an > old code and the passing of parameters in such way has been working fine on > oracledb. So is this something the cx_Oracle does not support? IF it is > oracle issue I wonder whey it did not give the error when we use oracledb. > > > > Regards, > > Wah Meng > > ________________________________ > > From: Amaury Forgeot d'Arc [mailto:ama...@gm...] > Sent: Wednesday, November 30, 2011 9:06 PM > > To: cx-...@li... > Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be > bound to PL/SQL statements > > > > 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...> > >>> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in >>> (:1) and LOT_ID = :2 and ......." > > This cannot work with Oracle. A placeholder can only replace one value. > > (Think of the IN operator as a sequence of OR conditions) > > Yes, this is a long standing issue with Oracle. > > I suggest to try with %s formatting. > > > > -- > Amaury Forgeot d'Arc > > ------------------------------------------------------------------------------ > 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. IT sense. And common sense. > http://p.sf.net/sfu/splunk-novd2d > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > ------------------------------------------------------------------------------ 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. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d _______________________________________________ cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users |
|
From: Wong W. Meng-R. <r3...@fr...> - 2011-12-01 10:58:35
|
Hello Anthony,
I tried to simulate and I think the result is what you meant. If the simulation is working, I can override inputtypehandler to convert a list to string. However the result shows that it is not working. The string output that is passed to Oracle doesn't seem to be recognized, though ORA-01484 is not thrown. It looks like we have no other way except to change our SQL not to use statements like "in (:1)"? This seems like an extensive change. Any other option I can take?
>>> cmd
'select user_id, user_first_name from users where user_id in (:1)'
>>> arg=('ftcs',)
>>> a.execute(cmd, arg)
[('ftcs', 'YYYY')]
>>> arg=("'ftcs'", )
>>> a.execute(cmd, arg)
[]
>>> arg=("'ftcs','R32813'",)
>>> a.execute(cmd, arg)
[]
>>>
Regards,
Wah Meng
-----Original Message-----
From: Wong Wah Meng-R32813
Sent: Thursday, December 01, 2011 1:00 PM
To: 'cx-...@li...'
Subject: RE: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements
Hi Anthony,
Thanks, and agreed with you on simply converting lists to string in arbitrary fashion is not the best way to handle this. I will feedback this to the application development team. Thanks a lot!
Regards,
Wah Meng
-----Original Message-----
From: Anthony Tuininga [mailto:ant...@gm...]
Sent: Thursday, December 01, 2011 12:29 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements
Oracle does not allow passing arrays to SQL statements, only PL/SQL
statements and this has been that way since as long as I have known
Oracle. The driver you were using before may have simply converted the
result to a string. cx_Oracle takes lists and converts them to PL/SQL
arrays -- which are not possible to be used in straight SQL
statements. So you will have to modify how you are using the driver.
In my opinion, converting lists to strings in some arbitrary fashion
is not the best way to handle such things!
Anthony
On Wed, Nov 30, 2011 at 7:07 PM, Wong Wah Meng-R32813
<r3...@fr...> wrote:
> Yes worst case is we will have to modify this SQL statement.
>
>
>
> However, is it a long standing issue with Oracle or cx_Oracle? This is an
> old code and the passing of parameters in such way has been working fine on
> oracledb. So is this something the cx_Oracle does not support? IF it is
> oracle issue I wonder whey it did not give the error when we use oracledb.
>
>
>
> Regards,
>
> Wah Meng
>
> ________________________________
>
> From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
> Sent: Wednesday, November 30, 2011 9:06 PM
>
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be
> bound to PL/SQL statements
>
>
>
> 2011/11/30 Wong Wah Meng-R32813 <r3...@fr...>
>
>>> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in
>>> (:1) and LOT_ID = :2 and ......."
>
> This cannot work with Oracle. A placeholder can only replace one value.
>
> (Think of the IN operator as a sequence of OR conditions)
>
> Yes, this is a long standing issue with Oracle.
>
> I suggest to try with %s formatting.
>
>
>
> --
> Amaury Forgeot d'Arc
>
> ------------------------------------------------------------------------------
> 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. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
------------------------------------------------------------------------------
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. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Amaury F. d'A. <ama...@gm...> - 2011-12-01 12:19:12
|
2011/12/1 Wong Wah Meng-R32813 <r3...@fr...> > It looks like we have no other way except to change our SQL not to use > statements like "in (:1)"? Exactly. Oracle (the SQL server, not cx_Oracle) does not allow list of values in bind variables. Use something like: "... where x in (%s) ..." % ', '.join(list_of_values) If it worked with oracledb, it's probably because oracledb does not really use bind variables, but internally format the query with %s or something similar. cx_Oracle really passes the sql string to the Oracle server unmodified, so you are subject to the limitations of the Oracle server. -- Amaury Forgeot d'Arc |
|
From: Christopher J. <chr...@or...> - 2011-12-02 19:39:47
|
On 12/01/2011 02:43 AM, Wong Wah Meng-R32813 wrote: > It looks like we have no other way except to change our SQL not to use statements like "in (:1)"? Correct, as others have said. The bind variable is only ever treated as a single piece of anonymous data by the Oracle DB. Tom Kyte has some discussions on "Varying IN Lists" in an old article http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html I cover the same topic a bit from from the binding perspective in PHP on page p146 of http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html -- Email: chr...@or... Tel: +1 650 506 8630 Blog: http://blogs.oracle.com/opal/ |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X