Thread: [cx-oracle-users] ORA-01790 error on union all with nullable bind variables
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Mikhail N. <mn...@ya...> - 2016-04-18 07:50:11
|
Hello everyone!
Does anybody know why this query fails with ORA-01790?
```
>>> import cx_Oracle
>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
>>> cur = con.cursor()
>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
arg0=None)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
as corresponding expression
```
It seems to me that bind variables passes as empty string '' and not as
NULL value.
Moreover, when I execute this query in oracle sqldeveloper I had the
same exception. Is it some oracle "feature"?
P.S.: The purpose of this question is the bug in django:
https://code.djangoproject.com/ticket/22669
--
Mikhail <mn...@ya...>
|
|
From: Massa, H. A. <ch...@gh...> - 2016-04-18 08:04:44
|
>
>
> Does anybody know why this query fails with ORA-01790?
>
> ```
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
> >>> cur = con.cursor()
> >>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
> arg0=None)
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
> as corresponding expression
> ```
>
> It seems to me that bind variables passes as empty string '' and not as
> NULL value.
>
most likely it is connected with this:
http://stackoverflow.com/questions/13278773/null-vs-empty-string-in-oracle
empty varchar2 and NULL being treated as equal in Oracle.
so, my suggestion, in the select :arg0 from dual cast :argo as numeric.
Harald
--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
Amtsgericht Stuttgart, HRB 734971
|
|
From: Jani T. <re...@gm...> - 2016-04-18 08:07:00
|
In Oracle "DUAL" is just a table with column named "DUMMY" which is
VARCHAR2(1).
Treating None with proper datatype might require some additional work
(IOW, you need to state that your argument is type of varchar).
On 18.04.2016 10:31, Mikhail Nacharov wrote:
> Hello everyone!
>
> Does anybody know why this query fails with ORA-01790?
>
> ```
>>>> import cx_Oracle
>>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
>>>> cur = con.cursor()
>>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
> arg0=None)
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
> as corresponding expression
> ```
>
> It seems to me that bind variables passes as empty string '' and not as
> NULL value.
> Moreover, when I execute this query in oracle sqldeveloper I had the
> same exception. Is it some oracle "feature"?
>
> P.S.: The purpose of this question is the bug in django:
> https://code.djangoproject.com/ticket/22669
>
>
|
|
From: Chris G. <chr...@to...> - 2016-04-18 08:27:39
|
It's irrelevant what the column type of DUMMY is on DUAL because the query
doesn't select the column.
The problem is that the data type of the returned column is implicitly
numeric from the first part of the unioned query, and a string in the
second part. The column types have to be the same in both parts. So the
best solution would be to use CAST as was stated in an earlier reply.
eg
select 1 from dual
union
select cast(:var as number) from dual
On 18 April 2016 at 09:06, Jani Tiainen <re...@gm...> wrote:
> In Oracle "DUAL" is just a table with column named "DUMMY" which is
> VARCHAR2(1).
>
> Treating None with proper datatype might require some additional work
> (IOW, you need to state that your argument is type of varchar).
>
> On 18.04.2016 10:31, Mikhail Nacharov wrote:
> > Hello everyone!
> >
> > Does anybody know why this query fails with ORA-01790?
> >
> > ```
> >>>> import cx_Oracle
> >>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
> >>>> cur = con.cursor()
> >>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
> > arg0=None)
> > Traceback (most recent call last):
> > File "<stdin>", line 1, in <module>
> > cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
> > as corresponding expression
> > ```
> >
> > It seems to me that bind variables passes as empty string '' and not as
> > NULL value.
> > Moreover, when I execute this query in oracle sqldeveloper I had the
> > same exception. Is it some oracle "feature"?
> >
> > P.S.: The purpose of this question is the bug in django:
> > https://code.djangoproject.com/ticket/22669
> >
> >
>
>
>
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications
> Manager
> Applications Manager provides deep performance insights into multiple
> tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Mikhail N. <mn...@ya...> - 2016-04-18 08:43:20
|
This solution works fine and I will probably use it.
But.. Is there a way to make oracle treats bind variable with None value
as NULL? I am asking because using null in query directly works
perfectly with any type of column
```
>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT null FROM dual')
<cx_Oracle.Cursor on <cx_Oracle.Connection to django@127.0.0.1/orcl>>
>>> _.fetchall()
[(1,), (None,)]
```
On Mon, 2016-04-18 at 09:27 +0100, Chris Gould wrote:
> It's irrelevant what the column type of DUMMY is on DUAL because the query
> doesn't select the column.
> The problem is that the data type of the returned column is implicitly
> numeric from the first part of the unioned query, and a string in the
> second part. The column types have to be the same in both parts. So the
> best solution would be to use CAST as was stated in an earlier reply.
> eg
>
> select 1 from dual
> union
> select cast(:var as number) from dual
>
> On 18 April 2016 at 09:06, Jani Tiainen <re...@gm...> wrote:
>
> > In Oracle "DUAL" is just a table with column named "DUMMY" which is
> > VARCHAR2(1).
> >
> > Treating None with proper datatype might require some additional work
> > (IOW, you need to state that your argument is type of varchar).
> >
> > On 18.04.2016 10:31, Mikhail Nacharov wrote:
> > > Hello everyone!
> > >
> > > Does anybody know why this query fails with ORA-01790?
> > >
> > > ```
> > >>>> import cx_Oracle
> > >>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
> > >>>> cur = con.cursor()
> > >>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
> > > arg0=None)
> > > Traceback (most recent call last):
> > > File "<stdin>", line 1, in <module>
> > > cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
> > > as corresponding expression
> > > ```
> > >
> > > It seems to me that bind variables passes as empty string '' and not as
> > > NULL value.
> > > Moreover, when I execute this query in oracle sqldeveloper I had the
> > > same exception. Is it some oracle "feature"?
> > >
> > > P.S.: The purpose of this question is the bug in django:
> > > https://code.djangoproject.com/ticket/22669
> > >
> > >
> >
> >
> >
> > ------------------------------------------------------------------------------
> > Find and fix application performance issues faster with Applications
> > Manager
> > Applications Manager provides deep performance insights into multiple
> > tiers of
> > your business applications. It resolves application problems quickly and
> > reduces your MTTR. Get your free trial!
> > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications Manager
> Applications Manager provides deep performance insights into multiple tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________ cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Anthony T. <ant...@gm...> - 2016-04-18 13:19:41
|
Yes. cx_Oracle assumes that None is bound as a string. You can tell it you
know better. :-)
cursor.setinputsizes(arg0 = cx_Oracle.NUMBER)
cursor.execute("select 1 from dual union all select :arg0 from dual", arg0
= None)
cursor.fetchall()
[(1,), (None,)]
Anthony
On Mon, Apr 18, 2016 at 2:43 AM, Mikhail Nacharov <mn...@ya...> wrote:
> This solution works fine and I will probably use it.
>
> But.. Is there a way to make oracle treats bind variable with None value
> as NULL? I am asking because using null in query directly works
> perfectly with any type of column
>
> ```
> >>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT null FROM dual')
> <cx_Oracle.Cursor on <cx_Oracle.Connection to django@127.0.0.1/orcl>>
> >>> _.fetchall()
> [(1,), (None,)]
> ```
>
>
>
> On Mon, 2016-04-18 at 09:27 +0100, Chris Gould wrote:
> > It's irrelevant what the column type of DUMMY is on DUAL because the
> query
> > doesn't select the column.
> > The problem is that the data type of the returned column is implicitly
> > numeric from the first part of the unioned query, and a string in the
> > second part. The column types have to be the same in both parts. So the
> > best solution would be to use CAST as was stated in an earlier reply.
> > eg
> >
> > select 1 from dual
> > union
> > select cast(:var as number) from dual
> >
> > On 18 April 2016 at 09:06, Jani Tiainen <re...@gm...> wrote:
> >
> > > In Oracle "DUAL" is just a table with column named "DUMMY" which is
> > > VARCHAR2(1).
> > >
> > > Treating None with proper datatype might require some additional work
> > > (IOW, you need to state that your argument is type of varchar).
> > >
> > > On 18.04.2016 10:31, Mikhail Nacharov wrote:
> > > > Hello everyone!
> > > >
> > > > Does anybody know why this query fails with ORA-01790?
> > > >
> > > > ```
> > > >>>> import cx_Oracle
> > > >>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
> > > >>>> cur = con.cursor()
> > > >>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
> > > > arg0=None)
> > > > Traceback (most recent call last):
> > > > File "<stdin>", line 1, in <module>
> > > > cx_Oracle.DatabaseError: ORA-01790: expression must have same
> datatype
> > > > as corresponding expression
> > > > ```
> > > >
> > > > It seems to me that bind variables passes as empty string '' and not
> as
> > > > NULL value.
> > > > Moreover, when I execute this query in oracle sqldeveloper I had the
> > > > same exception. Is it some oracle "feature"?
> > > >
> > > > P.S.: The purpose of this question is the bug in django:
> > > > https://code.djangoproject.com/ticket/22669
> > > >
> > > >
> > >
> > >
> > >
> > >
> ------------------------------------------------------------------------------
> > > Find and fix application performance issues faster with Applications
> > > Manager
> > > Applications Manager provides deep performance insights into multiple
> > > tiers of
> > > your business applications. It resolves application problems quickly
> and
> > > reduces your MTTR. Get your free trial!
> > > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> ------------------------------------------------------------------------------
> > Find and fix application performance issues faster with Applications
> Manager
> > Applications Manager provides deep performance insights into multiple
> tiers of
> > your business applications. It resolves application problems quickly and
> > reduces your MTTR. Get your free trial!
> > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > _______________________________________________ cx-oracle-users mailing
> list cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications
> Manager
> Applications Manager provides deep performance insights into multiple
> tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Jani T. <re...@gm...> - 2016-04-19 05:06:21
|
Right, that was it. :)
I remember that we hit this exact problem in Django Oracle GIS backend
with null geometries quite a long time ago.
On 18.04.2016 16:19, Anthony Tuininga wrote:
> Yes. cx_Oracle assumes that None is bound as a string. You can tell it
> you know better. :-)
>
> cursor.setinputsizes(arg0 = cx_Oracle.NUMBER)
> cursor.execute("select 1 from dual union all select :arg0 from dual",
> arg0 = None)
> cursor.fetchall()
> [(1,), (None,)]
>
> Anthony
>
>
> On Mon, Apr 18, 2016 at 2:43 AM, Mikhail Nacharov <mn...@ya...
> <mailto:mn...@ya...>> wrote:
>
> This solution works fine and I will probably use it.
>
> But.. Is there a way to make oracle treats bind variable with None
> value
> as NULL? I am asking because using null in query directly works
> perfectly with any type of column
>
> ```
> >>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT null FROM dual')
> <cx_Oracle.Cursor on <cx_Oracle.Connection to
> django@127.0.0.1/orcl <http://django@127.0.0.1/orcl>>>
> >>> _.fetchall()
> [(1,), (None,)]
> ```
>
>
>
> On Mon, 2016-04-18 at 09:27 +0100, Chris Gould wrote:
> > It's irrelevant what the column type of DUMMY is on DUAL because
> the query
> > doesn't select the column.
> > The problem is that the data type of the returned column is
> implicitly
> > numeric from the first part of the unioned query, and a string
> in the
> > second part. The column types have to be the same in both
> parts. So the
> > best solution would be to use CAST as was stated in an earlier
> reply.
> > eg
> >
> > select 1 from dual
> > union
> > select cast(:var as number) from dual
> >
> > On 18 April 2016 at 09:06, Jani Tiainen <re...@gm...
> <mailto:re...@gm...>> wrote:
> >
> > > In Oracle "DUAL" is just a table with column named "DUMMY"
> which is
> > > VARCHAR2(1).
> > >
> > > Treating None with proper datatype might require some
> additional work
> > > (IOW, you need to state that your argument is type of varchar).
> > >
> > > On 18.04.2016 10:31, Mikhail Nacharov wrote:
> > > > Hello everyone!
> > > >
> > > > Does anybody know why this query fails with ORA-01790?
> > > >
> > > > ```
> > > >>>> import cx_Oracle
> > > >>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl
> <http://django@127.0.0.1/orcl>')
> > > >>>> cur = con.cursor()
> > > >>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0
> FROM dual',
> > > > arg0=None)
> > > > Traceback (most recent call last):
> > > > File "<stdin>", line 1, in <module>
> > > > cx_Oracle.DatabaseError: ORA-01790: expression must have
> same datatype
> > > > as corresponding expression
> > > > ```
> > > >
> > > > It seems to me that bind variables passes as empty string ''
> and not as
> > > > NULL value.
> > > > Moreover, when I execute this query in oracle sqldeveloper I
> had the
> > > > same exception. Is it some oracle "feature"?
> > > >
> > > > P.S.: The purpose of this question is the bug in django:
> > > > https://code.djangoproject.com/ticket/22669
> > > >
> > > >
> > >
> > >
> > >
> > >
> ------------------------------------------------------------------------------
> > > Find and fix application performance issues faster with
> Applications
> > > Manager
> > > Applications Manager provides deep performance insights into
> multiple
> > > tiers of
> > > your business applications. It resolves application problems
> quickly and
> > > reduces your MTTR. Get your free trial!
> > > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> <mailto:cx-...@li...>
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> ------------------------------------------------------------------------------
> > Find and fix application performance issues faster with
> Applications Manager
> > Applications Manager provides deep performance insights into
> multiple tiers of
> > your business applications. It resolves application problems
> quickly and
> > reduces your MTTR. Get your free trial!
> > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > _______________________________________________ cx-oracle-users
> mailing list cx-...@li...
> <mailto:cx-...@li...>
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with
> Applications Manager
> Applications Manager provides deep performance insights into
> multiple tiers of
> your business applications. It resolves application problems
> quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> <mailto:cx-...@li...>
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
>
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications Manager
> Applications Manager provides deep performance insights into multiple tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Mikhail N. <mn...@ya...> - 2016-04-19 05:46:37
|
I guess it's better approach then calling cast inside queries. I ask a
question to devs about it.
Thanks!
On Tue, 2016-04-19 at 08:06 +0300, Jani Tiainen wrote:
> Right, that was it. :)
>
> I remember that we hit this exact problem in Django Oracle GIS
> backend
> with null geometries quite a long time ago.
>
> On 18.04.2016 16:19, Anthony Tuininga wrote:
> > Yes. cx_Oracle assumes that None is bound as a string. You can tell
> it
> > you know better. :-)
> >
> > cursor.setinputsizes(arg0 = cx_Oracle.NUMBER)
> > cursor.execute("select 1 from dual union all select :arg0 from
> dual",
> > arg0 = None)
> > cursor.fetchall()
> > [(1,), (None,)]
> >
> > Anthony
> >
|
|
From: Doug H. <djn...@gm...> - 2016-04-29 18:17:32
|
Cast :arg0 to a numeric type.
... select cast(:argo) as number ...
On Apr 18, 2016 1:50 AM, "Mikhail Nacharov" <mn...@ya...> wrote:
Hello everyone!
Does anybody know why this query fails with ORA-01790?
```
>>> import cx_Oracle
>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
>>> cur = con.cursor()
>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
arg0=None)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
as corresponding expression
```
It seems to me that bind variables passes as empty string '' and not as
NULL value.
Moreover, when I execute this query in oracle sqldeveloper I had the
same exception. Is it some oracle "feature"?
P.S.: The purpose of this question is the bug in django:
https://code.djangoproject.com/ticket/22669
--
Mikhail <mn...@ya...>
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers
of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
×
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