Re: [cx-oracle-users] ORA-01790 error on union all with nullable bind variables

[Anthony T. <ant...@gm...>]

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
>