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

[Chris G. <chr...@to...>]

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
>