cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] Wheels again

[Kubo T. <ku...@ji...>]

Hi,

I know a little about python and nothing about wheels.
However I have three ideas.

1. Distribute cx_Oracle compiled for Oracle 10g.

Oracle client library ABI keeps backward compatibility on Windows.
When a binary file is compiled for Oracle 10g, it works with Oracle
10g, 11g and 12c.
However it cannot use features depends on Oracle 11g and 12c clients.

2. Check Oracle client version at runtime.

This is ideal for pre-compiled packages but not practical because this needs
too many changes in cx_Oracle.

Add the following code to Module_Initialize() in cx_Oracle.c

   HMODULE hMod = GetModuleHandle("OCI.DLL");
   void (*func)(sword*, sword*, sword*, sword*, sword*) = (void
(*)(sword*, sword*, sword*, sword*, sword*))GetProcAddress(hMod,
"OCIClientVersion");
   if (func == NULL) {
     oracle_version_hex = ORACLE_VERSION(10, 1)
   } else {
     sword major, minor, update, patch, port;
     (*func)(&major, &minor, &update, &patch, &port);
     oracle_version_hex = ORACLE_VERSION(major, minor);
   }

and rewrite code such as
    #if ORACLE_VERSION_HEX >= ORACLE_VERSION(11,1)
      ...
    #endif
to
    if (oracle_client_hex >= ORACLE_VERSION(11,1)) {
      ...
    }

Use function pointers retrieved by GetProcAddress() for functions
added in Oracle 11g and 12c,

3. Add a bootstrap module if wheels can include more than one module.

Compile cx_Oracle for Oracle 10.1, 10.2, 11.1, 11.2 and 12.1 and rename them
to cx_Oracle.10.1.dll, cx_Oracle.10.2.dll, cx_Oracle.11.1.dll,
cx_Oracle.11.2.dll and cx_Oracle.12.1.dll respectively.

Compile the following code as cx_Oracle.dll.

    // This is incomplete code. no error checks.

    typedef int sword;

    static void *get_init_func(const char *init_func_name)
    {
      HMODULE hMod;
      void (*func)(sword*, sword*, sword*, sword*, sword*);
      char dll_name[30];
      char dll_full_path[MAX_PATH];

      // Get the Oracle client version and fill dll_name.
      hMod = LoadLibrary("OCI.DLL");
      func = (void (*)(sword*, sword*, sword*, sword*,
sword*))GetProcAddress(hMod, "OCIClientVersion");
      if (func == NULL) {
        strcpy(dll_name, "cx_Oracle.10.1.dll");
      } else {
        sword major, minor, update, patch, port;
        (*func)(&major, &minor, &update, &patch, &port);
        sprintf(dll_name, "cx_Oracle.%d.%d.dll", major, minor);
      }
      FreeLibrary(hMod);

      // get the current module handle.
      GetModuleHandleEx(GET_MODULE_HANDLE_EX_FLAG_FROM_ADDRESS,
&get_init_func, &hMod);
      ...  fill dll_full_path with the directory name containing the
current module by using GetModuleFileName(hMod, ...).
      ...  append dll_name to dll_full_path.

      // Load the real cx_Oracle module and get the module initializer function.
      hMod = LoadLibrary(dll_full_path);
      return GetProcAddress(hMod, init_func_name);
    }

    #if PY_MAJOR_VERSION >= 3
    PyMODINIT_FUNC PyInit_cx_Oracle(void)
    {
      PyObject *(*init)(void) =
(PyObject*(*)(void))get_init_func("PyInit_cx_Oracle");
      return (*init)();
    }
    #else
    void initcx_Oracle(void)
    {
      void (*init)(void) = (void(*)(void))get_init_func("initcx_Oracle");
      (*init)();
    }
    #endif


Put cx_Oracle.dll, cx_Oracle.10.1.dll, cx_Oracle.10.2.dll,
cx_Oracle.11.1.dll, cx_Oracle.11.2.dll
and cx_Oracle.12.1.dll in a directory.

[cx-oracle-users] Wheels again

[Adam K. <ad...@ke...>]

Hi all,

I've hacked together some wheels for Windows at work and am wondering 
what the difficulty is to create wheels and publish them to pypi?

I can't reply to this thread, but have read it:
https://sourceforge.net/p/cx-oracle/mailman/message/32861443/

Anthony says:
 > The problem is that Oracle has a different ABI for Oracle 10, 11
 > and 12 but the Python wheel packaging format doesn't allow for
 > that concept

I don't fully understand the implications of that, but what I've done is:

     wheel convert cx_Oracle-5.1.3-12c.win32-py2.7.exe

And then a few lines of python that go into the created wheel and update 
the version in dir names, METADATA, metadata.json and RECORD from 5.1.3 
to 5.1.3+12c.

(I know this is a hacky way to do it, but easier than figuring out how 
to compile cx_Oracle on Windows)

Now, this seems to work for us, but, obviously it creates different 
versions of cx_Oracle for the same version number. Is that a problem 
though? I can't think of when you'd want cx_Oracle 11g and 12c installed 
in the same installation or virtualenv and am not sure that the current 
process allows that. Perhaps this won't work very well when using >= 
version specifications and a different versioning method might be needed?

One alternative to just having different versions is to have different 
packages - cx_Oracle_11g, cx_Oracle_12c.

Any thoughts on this? Am I missing some obvious problems with this? I 
certainly don't pretend to fully understand all ways in which cx_Oracle 
is used. It would be great to just have wheels for Windows, and I am 
willing to help however I can.

Cheers,

Adam

Re: [cx-oracle-users] make cx_Oracle bind to a specific IP address or network interface?

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

Hi Dan,

Not sure if this will help or not....but it is worth a shot. :-)

http://stackoverflow.com/questions/27032429/how-to-bind-a-raw-socket-to-a-specific-interface-using-python-in-linux-centos

Anthony

On Tue, Apr 19, 2016 at 6:42 PM, Daniel Lenski <dl...@gm...> wrote:

> Hi all,
>
> Is there a way to make sure that cx_Oracle uses a specific network
> interface or IP address for its connections to Oracle server? This
> would be something comparable to "ssh -b BIND_ADDRESS".
>
> The reason I need this is because I'm using a split VPN tunnel to
> connect to a client's Oracle server, and I get errors when I tried
> to connect using the non-VPN network interface. Because I'm mostly
> developing and testing as a non-privileged user, adding a route that
> forces the client server connections to a specific interface isn't ideal.
>
> I thought this might be an Oracle client library reponsibility, and
> looked through the Oracle 11g environment variables in the documentation,
> however I don't see anything relevant:
>
> https://docs.oracle.com/cd/E11882_01/server.112/e10839/admin_ora.htm#UNXAR116
>
> Any suggestions on how to make cx_Oracle use a specific interface?
>
> Thanks,
> Dan
>
>
>
> ------------------------------------------------------------------------------
> 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
>

[cx-oracle-users] make cx_Oracle bind to a specific IP address or network interface?

[Daniel L. <dl...@gm...>]

Hi all,

Is there a way to make sure that cx_Oracle uses a specific network
interface or IP address for its connections to Oracle server? This
would be something comparable to "ssh -b BIND_ADDRESS".

The reason I need this is because I'm using a split VPN tunnel to
connect to a client's Oracle server, and I get errors when I tried
to connect using the non-VPN network interface. Because I'm mostly
developing and testing as a non-privileged user, adding a route that
forces the client server connections to a specific interface isn't ideal.

I thought this might be an Oracle client library reponsibility, and
looked through the Oracle 11g environment variables in the documentation, 
however I don't see anything relevant: 
https://docs.oracle.com/cd/E11882_01/server.112/e10839/admin_ora.htm#UNXAR116

Any suggestions on how to make cx_Oracle use a specific interface?

Thanks,
Dan

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

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

I guess it's better approach then calling cast inside queries. I ask a
question to devs about it.

Thanks!


On Tue, 2016-04-19 at 08:06 +0300, Jani Tiainen wrote:
> 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
> >

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

[Jani T. <re...@gm...>]

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

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
>

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

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
>

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

[Jani T. <re...@gm...>]

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

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

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

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

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


-- 
Mikhail <mn...@ya...>

[cx-oracle-users] cx_Oracle and OCI callbacks

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

All,

A little over a decade ago I added partial support for callbacks on OCI
functions. The documentation states that it is highly experimental and
should be used with caution. In fact, it was an idea that never really bore
any fruit and I have never personally used it. Subclassing connections and
cursors allows you to hook anything you would like in cx_Oracle's code
already and attempting to use the code on a connection not created by
cx_Oracle is difficult at best and was of no practical use. As such, I am
planning on dropping this code after 5.3 is released. If anyone out there
has found a use for this code and would appreciate it remaining, please let
me know! Thanks.

Anthony

Re: [cx-oracle-users] New features?

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

On Mon, Mar 7, 2016 at 8:30 AM, Walter Dörwald <wa...@li...>
wrote:

> On 7 Mar 2016, at 16:03, Anthony Tuininga wrote:
> >> BTW, it would be great if custom objects had a repr that at least
> >> displayed the underlying Oracle type, not just:
> >>
> >>  <cx_Oracle.Object object at 0x108b8f450>
> >>
> >> i.e. something like
> >>
> >>  <cx_Oracle.Object object type='INTEGERS' at 0x108b8f450>
>

I can buy that. :-) I have made that change.

Anthony

Re: [cx-oracle-users] New features?

[Walter D. <wa...@li...>]

On 7 Mar 2016, at 16:03, Anthony Tuininga wrote:

> Hi Walter,
>
> Yes, that was indeed an issue. Apparently data with a type of
> OCI_TYPECODE_INTEGER is actually stored as OCINumber, not as a native
> integer. So as long as you remain in Python all is well. But as soon 
> as you
> try to use it within PL/SQL you get strange results as you noted 
> above.
> This has been corrected. Thanks!

I tried it again with the new version, and now it works. Thanks!

And it works in views too:

create or replace view foo as
   select
     cast(multiset(select 1 from dual union all
                   select 2 from dual union all
                   select 3 from dual union all
                   select 4 from dual
     ) as integers) as ids
   from
	dual;

  >> from ll import orasql
  >> db = orasql.connect('user/pwd@db')
  >> c = db.cursor()
  >> c.execute("select * from foo")
  >> <ll.orasql.Cursor statement='select * from foo' at 0x11084a048>
  >> r = c.fetchone()
  >> r
  >> <ll.orasql.Record ids=<cx_Oracle.Object WALTER.INTEGERS> at 
0x1149d8240>
  >> r.ids
  >> <cx_Oracle.Object WALTER.INTEGERS>
  >> r.ids.aslist()
  >> [1, 2, 3, 4]

> I'll look into adding the custom repr as well. I agree that would be
> useful. :-)

<cx_Oracle.Object WALTER.INTEGERS> definitely is an improvement. However 
the object address should remain part of the object repr so that 
different objects are distinguishable, i.e. the repr should probably be:

    <cx_Oracle.Object WALTER.INTEGERS at 0x12345678>

> Anthony

Servus,
    Walter

> On Mon, Mar 7, 2016 at 3:06 AM, Walter Dörwald 
> <wa...@li...>
> wrote:
>
>> On 6 Mar 2016, at 3:23, Anthony Tuininga wrote:
>>
>>> Hi Walter,
>>>
>>> Apologies for the lengthy delay in responding to you!
>>>
>>> The reason for the failure was that integer was not supported (it is
>>> now,
>>> though). If you had used number or varchar2 instead all would have
>>> worked
>>> as expected!
>>>
>>> I also added support for the following, as suggested (thanks for the
>>> suggestion!)
>>>
>>> typeObj = connection.gettype("INTEGERS")
>>> obj = typeObj.newobject([1, 2, 3, 4])
>>> obj2 = typeObj([1, 2, 3, 4])
>>>
>>> Let me know if you have any other difficulties or suggestions! 
>>> Thanks.
>>
>> Now I can create INTEGERS objects. However there still seems to be a
>> problem. I tried the following:
>>
>> import cx_Oracle
>>
>> db = cx_Oracle.connect("user/pwd@db")
>> c = db.cursor()
>>
>> c.execute("create type integers as table of integer")
>>
>> c.execute("create table debug (dbg_text varchar2(4000), dbg_time
>> timestamp)")
>>
>> c.execute("""
>>   create or replace procedure debug_insert(p_dbg_text varchar2)
>>   as
>>   pragma autonomous_transaction;
>>   begin
>>     insert into debug (dbg_text, dbg_time) values (p_dbg_text,
>> systimestamp);
>>     commit;
>>   end;
>> """)
>>
>> c.execute("""
>>   create or replace function SEARCH
>>   (
>>     p_ids in integers,
>>     p_searchvalue in integer
>>   )
>>   return integer
>>   as
>>   begin
>>     debug_insert('searching for ' || p_searchvalue);
>>     for i in 1..p_ids.count loop
>>       debug_insert('testing ' || p_ids(i));
>>       if p_ids(i) = p_searchvalue then
>>         debug_insert('found');
>>         return 1;
>>       end if;
>>     end loop;
>>     debug_insert('not found');
>>     return 0;
>>   end;
>> """)
>>
>> integers = db.gettype("INTEGERS")
>>
>> print(c.callfunc("SEARCH", int, [integers([1,2,3,4]), 2]))
>>
>> The output of this script is 0. However I would have expected it to 
>> be
>> 1.
>>
>> Looking into the DEBUG table gives me the following:
>>
>>  searching for 2
>>
>>  testing -~
>>
>>  testing
>>
>>  testing
>>
>> -0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125
>>
>>  testing
>>
>> -0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125
>>
>>  not found
>>
>> So either I'm doing something stupid, or passing an INTEGERS object
>> still has problems.
>>
>> BTW, it would be great if custom objects had a repr that at least
>> displayed the underlying Oracle type, not just:
>>
>>  <cx_Oracle.Object object at 0x108b8f450>
>>
>> i.e. something like
>>
>>  <cx_Oracle.Object object type='INTEGERS' at 0x108b8f450>
>>
>>> Anthony
>>> [...]
>>
>> Servus,
>>  Walter
>>
>>
>> ------------------------------------------------------------------------------
>> Transform Data into Opportunity.
>> Accelerate data analysis in your applications with
>> Intel Data Analytics Acceleration Library.
>> Click to learn more.
>> http://makebettercode.com/inteldaal-eval
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> ------------------------------------------------------------------------------
> Transform Data into Opportunity.
> Accelerate data analysis in your applications with
> Intel Data Analytics Acceleration Library.
> Click to learn more.
> http://makebettercode.com/inteldaal-eval_______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] New features?

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

Hi Walter,

Yes, that was indeed an issue. Apparently data with a type of
OCI_TYPECODE_INTEGER is actually stored as OCINumber, not as a native
integer. So as long as you remain in Python all is well. But as soon as you
try to use it within PL/SQL you get strange results as you noted above.
This has been corrected. Thanks!

I'll look into adding the custom repr as well. I agree that would be
useful. :-)

Anthony

On Mon, Mar 7, 2016 at 3:06 AM, Walter Dörwald <wa...@li...>
wrote:

> On 6 Mar 2016, at 3:23, Anthony Tuininga wrote:
>
> > Hi Walter,
> >
> > Apologies for the lengthy delay in responding to you!
> >
> > The reason for the failure was that integer was not supported (it is
> > now,
> > though). If you had used number or varchar2 instead all would have
> > worked
> > as expected!
> >
> > I also added support for the following, as suggested (thanks for the
> > suggestion!)
> >
> > typeObj = connection.gettype("INTEGERS")
> > obj = typeObj.newobject([1, 2, 3, 4])
> > obj2 = typeObj([1, 2, 3, 4])
> >
> > Let me know if you have any other difficulties or suggestions! Thanks.
>
> Now I can create INTEGERS objects. However there still seems to be a
> problem. I tried the following:
>
>    import cx_Oracle
>
>    db = cx_Oracle.connect("user/pwd@db")
>    c = db.cursor()
>
>    c.execute("create type integers as table of integer")
>
>    c.execute("create table debug (dbg_text varchar2(4000), dbg_time
> timestamp)")
>
>    c.execute("""
>      create or replace procedure debug_insert(p_dbg_text varchar2)
>      as
>      pragma autonomous_transaction;
>      begin
>        insert into debug (dbg_text, dbg_time) values (p_dbg_text,
> systimestamp);
>        commit;
>      end;
>    """)
>
>    c.execute("""
>      create or replace function SEARCH
>      (
>        p_ids in integers,
>        p_searchvalue in integer
>      )
>      return integer
>      as
>      begin
>        debug_insert('searching for ' || p_searchvalue);
>        for i in 1..p_ids.count loop
>          debug_insert('testing ' || p_ids(i));
>          if p_ids(i) = p_searchvalue then
>            debug_insert('found');
>            return 1;
>          end if;
>        end loop;
>        debug_insert('not found');
>        return 0;
>      end;
>    """)
>
>    integers = db.gettype("INTEGERS")
>
>    print(c.callfunc("SEARCH", int, [integers([1,2,3,4]), 2]))
>
> The output of this script is 0. However I would have expected it to be
> 1.
>
> Looking into the DEBUG table gives me the following:
>
>     searching for 2
>
>     testing -~
>
>     testing
>
>     testing
>
> -0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125
>
>     testing
>
> -0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125
>
>     not found
>
> So either I'm doing something stupid, or passing an INTEGERS object
> still has problems.
>
> BTW, it would be great if custom objects had a repr that at least
> displayed the underlying Oracle type, not just:
>
>     <cx_Oracle.Object object at 0x108b8f450>
>
> i.e. something like
>
>     <cx_Oracle.Object object type='INTEGERS' at 0x108b8f450>
>
> > Anthony
> > [...]
>
> Servus,
>     Walter
>
>
> ------------------------------------------------------------------------------
> Transform Data into Opportunity.
> Accelerate data analysis in your applications with
> Intel Data Analytics Acceleration Library.
> Click to learn more.
> http://makebettercode.com/inteldaal-eval
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] New features?

[Walter D. <wa...@li...>]

On 6 Mar 2016, at 3:23, Anthony Tuininga wrote:

> Hi Walter,
>
> Apologies for the lengthy delay in responding to you!
>
> The reason for the failure was that integer was not supported (it is 
> now,
> though). If you had used number or varchar2 instead all would have 
> worked
> as expected!
>
> I also added support for the following, as suggested (thanks for the
> suggestion!)
>
> typeObj = connection.gettype("INTEGERS")
> obj = typeObj.newobject([1, 2, 3, 4])
> obj2 = typeObj([1, 2, 3, 4])
>
> Let me know if you have any other difficulties or suggestions! Thanks.

Now I can create INTEGERS objects. However there still seems to be a 
problem. I tried the following:

   import cx_Oracle

   db = cx_Oracle.connect("user/pwd@db")
   c = db.cursor()

   c.execute("create type integers as table of integer")

   c.execute("create table debug (dbg_text varchar2(4000), dbg_time 
timestamp)")

   c.execute("""
     create or replace procedure debug_insert(p_dbg_text varchar2)
     as
     pragma autonomous_transaction;
     begin
       insert into debug (dbg_text, dbg_time) values (p_dbg_text, 
systimestamp);
       commit;
     end;
   """)

   c.execute("""
     create or replace function SEARCH
     (
       p_ids in integers,
       p_searchvalue in integer
     )
     return integer
     as
     begin
       debug_insert('searching for ' || p_searchvalue);
       for i in 1..p_ids.count loop
         debug_insert('testing ' || p_ids(i));
         if p_ids(i) = p_searchvalue then
           debug_insert('found');
           return 1;
         end if;
       end loop;
       debug_insert('not found');
       return 0;
     end;
   """)

   integers = db.gettype("INTEGERS")

   print(c.callfunc("SEARCH", int, [integers([1,2,3,4]), 2]))

The output of this script is 0. However I would have expected it to be 
1.

Looking into the DEBUG table gives me the following:

    searching for 2

    testing -~

    testing

    testing 
-0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125

    testing 
-0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125

    not found

So either I'm doing something stupid, or passing an INTEGERS object 
still has problems.

BTW, it would be great if custom objects had a repr that at least 
displayed the underlying Oracle type, not just:

    <cx_Oracle.Object object at 0x108b8f450>

i.e. something like

    <cx_Oracle.Object object type='INTEGERS' at 0x108b8f450>

> Anthony
> [...]

Servus,
    Walter

Re: [cx-oracle-users] New features?

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

Hi Walter,

Apologies for the lengthy delay in responding to you!

The reason for the failure was that integer was not supported (it is now,
though). If you had used number or varchar2 instead all would have worked
as expected!

I also added support for the following, as suggested (thanks for the
suggestion!)

typeObj = connection.gettype("INTEGERS")
obj = typeObj.newobject([1, 2, 3, 4])
obj2 = typeObj([1, 2, 3, 4])

Let me know if you have any other difficulties or suggestions! Thanks.

Anthony

On Wed, Feb 24, 2016 at 7:07 AM, Walter Dörwald <wa...@li...>
wrote:

> On 24 Feb 2016, at 0:30, Anthony Tuininga wrote:
>
> > On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald
> > <wa...@li...>
> > wrote:
> >
> >> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
> >>
> >>> All,
> >>>
> >>> I have recently been able to spend a bit more time on cx_Oracle and
> >>> would
> >>> like your feedback on what features would be of the greatest benefit
> >>> to
> >>> you. Besides the ones that are in 5.2.1, the following features have
> >>> been
> >>> committed:
> >>>
> >>> Added support for pickling/unpickling error objects
> >>> Added support for getting implicit results (Oracle Database 12.1)
> >>> Added support for Transaction Guard (Oracle Database 12.1)
> >>> Added support for setting max lifetime session of pool (Oracle
> >>> Database
> >>> 12.1)
> >>>
> >>> Any and all feedback appreciated.
> >>>
> >>> Anthony
> >>
> >> We have many procedures which have a parameter with type INTEGERS,
> >> which
> >> is defined as:
> >>
> >>  create or replace type integers as table of integer;
> >>
> >> I'd like to by able to such a procedure through cx_Oracle.
> >>
> >
> > This can now be done with the code in the source repository today.
> >
> > typeObj = connection.gettype("integers")
> > obj = typeObj.newobject()
> > obj.extend([1, 2, 3, 4])
> > cursor = connection.cursor()
> > cursor.callproc("someprocedure", (obj,))
>
> It doesn't seem to work for me. I get the following:
>
> $ python
> Python 3.5.1 (default, Jan 22 2016, 11:57:23)
> [GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.1.76)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> >> import cx_Oracle
> >> db = cx_Oracle.connect("user/pwd@db")
> >> integers = db.gettype("INTEGERS")
> >> i = integers.newobject()
> >> i.extend([1,2,3,4])
> Traceback (most recent call last):
>    File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data
> type 3
> >> ^D
>
> integers is defined as:
>
>     create or replace type INTEGERS as table of integer;
>
> I installed cx_Oracle via
>
> $ hg clone https://bitbucket.org/anthony_tuininga/cx_oracle
> $ cd cx_oracle
> $ pip install .
>
> BTW, it would be great if the type object could be called (just like a
> Python class) to create an instance, i.e. use:
>
>         i = integers([1, 2, 3, 4])
>
> instead of
>
>      i = integers.newobject()
>      i.extend([1,2,3,4])
>
> Servus,
>     Walter
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] New features?

[Walter D. <wa...@li...>]

On 24 Feb 2016, at 0:30, Anthony Tuininga wrote:

> On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald 
> <wa...@li...>
> wrote:
>
>> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
>>
>>> All,
>>>
>>> I have recently been able to spend a bit more time on cx_Oracle and
>>> would
>>> like your feedback on what features would be of the greatest benefit
>>> to
>>> you. Besides the ones that are in 5.2.1, the following features have
>>> been
>>> committed:
>>>
>>> Added support for pickling/unpickling error objects
>>> Added support for getting implicit results (Oracle Database 12.1)
>>> Added support for Transaction Guard (Oracle Database 12.1)
>>> Added support for setting max lifetime session of pool (Oracle
>>> Database
>>> 12.1)
>>>
>>> Any and all feedback appreciated.
>>>
>>> Anthony
>>
>> We have many procedures which have a parameter with type INTEGERS, 
>> which
>> is defined as:
>>
>>  create or replace type integers as table of integer;
>>
>> I'd like to by able to such a procedure through cx_Oracle.
>>
>
> This can now be done with the code in the source repository today.
>
> typeObj = connection.gettype("integers")
> obj = typeObj.newobject()
> obj.extend([1, 2, 3, 4])
> cursor = connection.cursor()
> cursor.callproc("someprocedure", (obj,))

It doesn't seem to work for me. I get the following:

$ python
Python 3.5.1 (default, Jan 22 2016, 11:57:23)
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.1.76)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>> import cx_Oracle
>> db = cx_Oracle.connect("user/pwd@db")
>> integers = db.gettype("INTEGERS")
>> i = integers.newobject()
>> i.extend([1,2,3,4])
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data 
type 3
>> ^D

integers is defined as:

    create or replace type INTEGERS as table of integer;

I installed cx_Oracle via

$ hg clone https://bitbucket.org/anthony_tuininga/cx_oracle
$ cd cx_oracle
$ pip install .

BTW, it would be great if the type object could be called (just like a 
Python class) to create an instance, i.e. use:

	i = integers([1, 2, 3, 4])

instead of

     i = integers.newobject()
     i.extend([1,2,3,4])

Servus,
    Walter

Re: [cx-oracle-users] New features?

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

On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald <wa...@li...>
wrote:

> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
>
> > All,
> >
> > I have recently been able to spend a bit more time on cx_Oracle and
> > would
> > like your feedback on what features would be of the greatest benefit
> > to
> > you. Besides the ones that are in 5.2.1, the following features have
> > been
> > committed:
> >
> > Added support for pickling/unpickling error objects
> > Added support for getting implicit results (Oracle Database 12.1)
> > Added support for Transaction Guard (Oracle Database 12.1)
> > Added support for setting max lifetime session of pool (Oracle
> > Database
> > 12.1)
> >
> > Any and all feedback appreciated.
> >
> > Anthony
>
> We have many procedures which have a parameter with type INTEGERS, which
> is defined as:
>
>     create or replace type integers as table of integer;
>
> I'd like to by able to such a procedure through cx_Oracle.
>

This can now be done with the code in the source repository today.

typeObj = connection.gettype("integers")
obj = typeObj.newobject()
obj.extend([1, 2, 3, 4])
cursor = connection.cursor()
cursor.callproc("someprocedure", (obj,))

Anthony

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

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

Hi Michael,

The current code in the source repository will allow you to do this now.
There is also an example that demonstrates how to create an SDO_GEOMETRY
object from scratch. This will (at some point) become 5.3 but you can use
it today if you're up for building it yourself -- since you're on Linux
that isn't too difficult.

Anthony

On Sun, Feb 7, 2016 at 7:13 AM, Michael Huber <mhu...@gm...> wrote:

> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store the
> content of this column to another table. If I do so, I'm receiving the
> following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back to
> an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Stuck at "Python version 3.5 required, which was not found in the registry."

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

Hi Greg,

I'm not sure why you are experiencing those issues....but apparently you
are not alone. Others have experienced such issues when attempting to
install 32-bit stuff on 64-bit Windows machines. You may wish to install
64-bit Python as well -- it may make it happier. :-) Once you have
everything you want you can always uninstall the 64-bit version -- assuming
that an upgrade to 64-bit is completely out of the works, of course! And if
it puts it in the wrong place, you can always move cx_Oracle.pyd to the
right place. That is the only file that really matters anyway.

Regarding the issue of vcvarsall.bat -- my guess is you have to run the
special command prompt that Microsoft provides that sets the environment
correctly....or set your environment correctly yourself. :-)

Failing that, you can send me an e-mail and I can reply with the .pyd file
directly (suitably renamed, of course, to get around the "security"
employed by Windows).

On Tue, Feb 16, 2016 at 9:33 AM, Tipps, Greg (Greg Tipps) <rt...@ut...>
wrote:

> Hello cx_Oracle users,
>
>
>
> I have a 64-bit Windows Server 2012 R2 box that is running 32-bit Python
> 2.7.4 and uses cx_Oracle to connect to an Oracle 11g database, and I am
> trying to upgrade to 32-bit Python 3.5.
>
>
>
> For Python 2.7.4 I was able to use the PyPi .exe installer (page link:
> https://pypi.python.org/pypi/cx_Oracle/), but the Python 3.5 installer
> (cx_Oracle-5.2.1-11g.win32-py3.5.exe) always gives me this error: “Python
> version 3.5 required, which was not found in the registry."
>
>
>
> Some Googling of that led me to a blog post (link:
> http://codeyarns.com/2012/04/28/python-version-not-found-in-registry-error/)
> where the author was able to get around this error by creating some new
> registry keys- It turned out that in their case, the module was looking for
> some values under HKEY_CURRENT_USER\SOFTWARE\Python\PythonCore\3.2 , and
>  those values were actually written by the Python installer at
> HKEY_LOCAL_MACHINE\SOFTWARE\Python\PythonCore\3.2. They were able to
> export the HKLM values, edit the .reg file, then import them into HKCU, and
> successfully install the module.
>
>
>
> I noticed that in my case, since I am working with 32-bit Python on a
> 64-bit system, those keys were written at
> HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\3.2, so I tried
> exporting the Wow6432Node\Python key and importing it in these three
> locations:
>
> HKEY_CURRENT_USER\SOFTWARE\Python
>
> HKEY_CURRENT_USER\SOFTWARE\Python\Wow6432Node
>
> HKEY_LOCAL_MACHINE\SOFTWARE\Python
>
>
>
> I rebooted the server after making these changes and ran the installer
> again, and was still met with the “Python version 3.5 required” error. I
> tried to find the registry path the installer is checking by inspecting the
> source code (https://bitbucket.org/anthony_tuininga/cx_oracle), but I
> wasn’t able to find that error string. Can anyone here point me in one
> direction or another?
>
>
>
> P.S. I’m trying to keep this message short, but: I have also tried to
> build & install the cx_Oracle module using pip, and while the server has 10
> separate versions of the MS Visual C++ redistributable and the 11GB MS
> Video Studio Community 2015 package installed, that build attempt always
> fails at “error: Unable to find vcvarsall.bat” so I’m hoping the registry
> issue with the binary installer might be easier to resolve.
>
>
>
> --Greg
>
>
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Stuck at "Python version 3.5 required, which was not found in the registry."

[Tipps, G. (G. Tipps) <rt...@ut...>]

Hello cx_Oracle users,

I have a 64-bit Windows Server 2012 R2 box that is running 32-bit Python 2.7.4 and uses cx_Oracle to connect to an Oracle 11g database, and I am trying to upgrade to 32-bit Python 3.5.

For Python 2.7.4 I was able to use the PyPi .exe installer (page link: https://pypi.python.org/pypi/cx_Oracle/), but the Python 3.5 installer (cx_Oracle-5.2.1-11g.win32-py3.5.exe) always gives me this error: "Python version 3.5 required, which was not found in the registry."

Some Googling of that led me to a blog post (link: http://codeyarns.com/2012/04/28/python-version-not-found-in-registry-error/) where the author was able to get around this error by creating some new registry keys- It turned out that in their case, the module was looking for some values under HKEY_CURRENT_USER\SOFTWARE\Python\PythonCore\3.2 , and  those values were actually written by the Python installer at HKEY_LOCAL_MACHINE\SOFTWARE\Python\PythonCore\3.2. They were able to export the HKLM values, edit the .reg file, then import them into HKCU, and successfully install the module.

I noticed that in my case, since I am working with 32-bit Python on a 64-bit system, those keys were written at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\3.2, so I tried exporting the Wow6432Node\Python key and importing it in these three locations:
HKEY_CURRENT_USER\SOFTWARE\Python
HKEY_CURRENT_USER\SOFTWARE\Python\Wow6432Node
HKEY_LOCAL_MACHINE\SOFTWARE\Python

I rebooted the server after making these changes and ran the installer again, and was still met with the "Python version 3.5 required" error. I tried to find the registry path the installer is checking by inspecting the source code (https://bitbucket.org/anthony_tuininga/cx_oracle), but I wasn't able to find that error string. Can anyone here point me in one direction or another?

P.S. I'm trying to keep this message short, but: I have also tried to build & install the cx_Oracle module using pip, and while the server has 10 separate versions of the MS Visual C++ redistributable and the 11GB MS Video Studio Community 2015 package installed, that build attempt always fails at "error: Unable to find vcvarsall.bat" so I'm hoping the registry issue with the binary installer might be easier to resolve.

--Greg

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

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

All,

I have implemented the ability to bind objects which you can see in the
checked-in code today. If you wish to try it out and let me know if your
case still does not work, that would be appreciated. I will be working on
an example for SDO_GEOMETRY so any one who has a good suggestion on what
ought to be in said example, that would also be appreciated.

You can take a look at the ObjectVar test case and the documentation which
shows the new capabilities but here is a brief example.

typeObj = connection.gettype(name)
obj = typeObj.newobject()
obj.ATTR1 = 5
obj.ATTR2 = "Some string"
cursor.callproc("someproc", (obj, 5))

Anthony

On Mon, Feb 8, 2016 at 6:54 AM, Anthony Tuininga <ant...@gm...
> wrote:

> Hi Michael,
>
> Jani is right in that cx_Oracle currently does not support binding
> objects....but I am working on changing that and hope to have that
> capability implemented soon. :-) I'll reply back again when that is done
> but if you need it immediately you'll have to follow the workaround Jani
> suggested.
>
> Anthony
>
> On Mon, Feb 8, 2016 at 2:33 AM, Jani Tiainen <re...@gm...> wrote:
>
>> Hi,
>>
>> Unfortunately cx_Oracle doesn't support writing user types to database,
>> only reading.
>>
>> There are few options to overcome your problem:
>>
>> Make output as varchar/clob like wkt. This is relatively slow.
>>
>> Another option is to use nice approach described here:
>> http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
>>
>>
>>
>> On 07.02.2016 16:13, Michael Huber wrote:
>>
>> Hi,
>>
>> I'm querying from a table with a SDO_GEOMETRY column and want to store
>> the content of this column to another table. If I do so, I'm receiving the
>> following exception:
>>
>> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
>> cx_Oracle.OBJECT
>>
>> Please find a basic example below:
>>
>> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
>> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
>> Type "help", "copyright", "credits" or "license" for more information.
>> >>> import cx_Oracle
>> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
>> >>> cur = con.cursor()
>> >>>
>> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
>> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname
>> :1521/TESTDB>>
>> >>> source_data = cur.fetchall()
>> >>>
>> >>> print(source_data)
>> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
>> >>>
>> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
>> [source_data[0][0], source_data[0][1], source_data[0][2] ])
>> Traceback (most recent call last):
>>   File "<stdin>", line 1, in <module>
>> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
>> cx_Oracle.OBJECT
>> >>>
>>
>> Is it not supported to write the content of a SDO_GEOMETRY column back to
>> an Oracle database? Thanks in advance!
>>
>> Best,
>> Micheal
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>>
>>
>>
>> _______________________________________________
>> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>> --
>> Jani Tiainen
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!
>> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

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

Hi Michael,

Jani is right in that cx_Oracle currently does not support binding
objects....but I am working on changing that and hope to have that
capability implemented soon. :-) I'll reply back again when that is done
but if you need it immediately you'll have to follow the workaround Jani
suggested.

Anthony

On Mon, Feb 8, 2016 at 2:33 AM, Jani Tiainen <re...@gm...> wrote:

> Hi,
>
> Unfortunately cx_Oracle doesn't support writing user types to database,
> only reading.
>
> There are few options to overcome your problem:
>
> Make output as varchar/clob like wkt. This is relatively slow.
>
> Another option is to use nice approach described here:
> http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
>
>
>
> On 07.02.2016 16:13, Michael Huber wrote:
>
> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store the
> content of this column to another table. If I do so, I'm receiving the
> following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back to
> an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>
>
>
> _______________________________________________
> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> --
> Jani Tiainen
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>