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

[Mikhail N. <mn...@ya...>]

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