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

[Massa, H. A. <ch...@gh...>]

>
>
> 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