Re: [cx-oracle-users] ORA-01790 error on union all with nullable bind variables
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] ORA-01790 error on union all with nullable bind variables
|
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
|
