Support for `fetch_lobs` and `fetch_decimal` per connection instead of globally

[doerwalter]

It would be great, if fetch_lobs and fetch_decimal were supported as parameters for oracledb.connect() directly instead of only as global configuration parameters in oracledb.defaults that only take effect on the next oracledb.connect() call.

So I would like to be able to do:

db = oracledb.connect("user/pwd@db", fetch_lobs=False)

instead of:

oracledb.defaults.fetch_lobs = False
db = oracledb.connect("user/pwd@db")

[anthony-tuininga]

I thought this might be coming. :-) It makes sense and we will definitely implement something llike it in a future release. In the meantime, note tht the oracledb.defaults.fetch_lobs value is examined every time an execute is performed and is not retained on the connection object in any way. So you don't have to create a new connection in order to change the value!

[doerwalter]

I thought this might be coming. :-)

Why is that?

It makes sense and we will definitely implement something llike it in a future release. In the meantime, note tht the oracledb.defaults.fetch_lobs value is examined every time an execute is performed and is not retained on the connection object in any way. So you don't have to create a new connection in order to change the value!

With oracledb version 1.0.1 it seems that the value of oracledb.defaults.fetch_lobs isn't checked when execute() is called, but seems to be retained by the cursor:

Python 3.10.4 (main, Apr 26 2022, 19:36:29) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.4.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 ❯ import oracledb
🐍 — 2 ❯ oracledb.defaults.fetch_lobs
🐍 — 2 ❮ True
🐍 — 3 ❯ db = oracledb.connect('user/pwd@db', config_dir="/Users/walter/oracle/instantclient_12_2/network/admin")
🐍 — 4 ❯ c = db.cursor()
🐍 — 5 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 5 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 6 ❯ c.fetchone()
🐍 — 6 ❮ (<oracledb.LOB at 0x105537fd0>,)
🐍 — 7 ❯ oracledb.defaults.fetch_lobs = False
🐍 — 8 ❯ c.fetchone()
🐍 — 8 ❮ (<oracledb.LOB at 0x1055373d0>,)
🐍 — 9 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 9 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 10 ❯ c.fetchone()
🐍 — 10 ❮ (<oracledb.LOB at 0x10604d6f0>,)
🐍 — 11 ❯ c = db.cursor()
🐍 — 12 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 12 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 13 ❯ c.fetchone()
🐍 — 13 ❮ ('<?ul4 field_errors_html(...',)
🐍 — 14 ❯

[anthony-tuininga]

I thought this might be coming. :-)

Why is that?

Just because it is a natural extension of the capability. Most people are going to want to simply set oracledb.defaults.fetch_lobs = False and never pay attention to it after that! But some will want to have the ability to fetch the LOBs directly, and adjusting it per connection (and probably per cursor) is likely desirable -- in the same way as is done for output type handlers and input type handlers.

It makes sense and we will definitely implement something llike it in a future release. In the meantime, note tht the oracledb.defaults.fetch_lobs value is examined every time an execute is performed and is not retained on the connection object in any way. So you don't have to create a new connection in order to change the value!

With oracledb version 1.0.1 it seems that the value of oracledb.defaults.fetch_lobs isn't checked when execute() is called, but seems to be retained by the cursor:

Python 3.10.4 (main, Apr 26 2022, 19:36:29) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.4.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 ❯ import oracledb
🐍 — 2 ❯ oracledb.defaults.fetch_lobs
🐍 — 2 ❮ True
🐍 — 3 ❯ db = oracledb.connect('user/pwd@db', config_dir="/Users/walter/oracle/instantclient_12_2/network/admin")
🐍 — 4 ❯ c = db.cursor()
🐍 — 5 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 5 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 6 ❯ c.fetchone()
🐍 — 6 ❮ (<oracledb.LOB at 0x105537fd0>,)
🐍 — 7 ❯ oracledb.defaults.fetch_lobs = False
🐍 — 8 ❯ c.fetchone()
🐍 — 8 ❮ (<oracledb.LOB at 0x1055373d0>,)
🐍 — 9 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 9 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 10 ❯ c.fetchone()
🐍 — 10 ❮ (<oracledb.LOB at 0x10604d6f0>,)
🐍 — 11 ❯ c = db.cursor()
🐍 — 12 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 12 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 13 ❯ c.fetchone()
🐍 — 13 ❮ ('<?ul4 field_errors_html(...',)
🐍 — 14 ❯

Ah! You are executing the same SQL -- so the code that builds the list of fetch variables only runs the first time and then retains them until different SQL is run or until a new cursor is built. There is code to check for changes to the output type handler but not for changes to the fetch_lobs or fetch_decimals values when executing identical SQL. That should be corrected as well. I'll take a note.

[doerwalter]

I thought this might be coming. :-)

Why is that?

Just because it is a natural extension of the capability. Most people are going to want to simply set oracledb.defaults.fetch_lobs = False and never pay attention to it after that! But some will want to have the ability to fetch the LOBs directly, and adjusting it per connection (and probably per cursor) is likely desirable -- in the same way as is done for output type handlers and input type handlers.

That's what I implemented in ll.orasql: https://python.livinglogic.de/orasql.html (see https://github.com/LivingLogic/LivingLogic.Python.xist/blob/rel-5-70/src/ll/orasql/__init__.py#L427)

It would be a shame, if I couldn't use the new oracledb functionality for that, and would still have to use outputtypehandler.

So, yes: Support for fetch_lobs and fetch_decimal on the cursor would be great. (And if the argument doesn't get passed to the Cursor constructor (or with a None value), the value configured for the connection should be used (and in turn the value in oracledb.defaults if it isn't configured on the connection)).

It makes sense and we will definitely implement something llike it in a future release. In the meantime, note tht the oracledb.defaults.fetch_lobs value is examined every time an execute is performed and is not retained on the connection object in any way. So you don't have to create a new connection in order to change the value!

With oracledb version 1.0.1 it seems that the value of oracledb.defaults.fetch_lobs isn't checked when execute() is called, but seems to be retained by the cursor:

Python 3.10.4 (main, Apr 26 2022, 19:36:29) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.4.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 ❯ import oracledb
🐍 — 2 ❯ oracledb.defaults.fetch_lobs
🐍 — 2 ❮ True
🐍 — 3 ❯ db = oracledb.connect('user/pwd@db', config_dir="/Users/walter/oracle/instantclient_12_2/network/admin")
🐍 — 4 ❯ c = db.cursor()
🐍 — 5 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 5 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 6 ❯ c.fetchone()
🐍 — 6 ❮ (<oracledb.LOB at 0x105537fd0>,)
🐍 — 7 ❯ oracledb.defaults.fetch_lobs = False
🐍 — 8 ❯ c.fetchone()
🐍 — 8 ❮ (<oracledb.LOB at 0x1055373d0>,)
🐍 — 9 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 9 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 10 ❯ c.fetchone()
🐍 — 10 ❮ (<oracledb.LOB at 0x10604d6f0>,)
🐍 — 11 ❯ c = db.cursor()
🐍 — 12 ❯ c.execute("select utv_source from internaltemplate_select")
🐍 — 12 ❮ <oracledb.Cursor on <oracledb.Connection to user@db>>
🐍 — 13 ❯ c.fetchone()
🐍 — 13 ❮ ('<?ul4 field_errors_html(...',)
🐍 — 14 ❯

Ah! You are executing the same SQL -- so the code that builds the list of fetch variables only runs the first time and then retains them until different SQL is run or until a new cursor is built. There is code to check for changes to the output type handler but not for changes to the fetch_lobs or fetch_decimals values when executing identical SQL. That should be corrected as well. I'll take a note.

OK, that explains the "erratic" behaviour: Sometimes the execute() call behaved as expected, sometimes it didn't.

[anthony-tuininga]

I recently pushed changes to allow the specification of fetch_lobs and fetch_decimals when calling cursor.execute() and the other methods that result in a query being executed. I have initiated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. I think this gives you the functionality requested in this enhancement request but if you think otherwise, please let me know!

Note that if you execute the same SQL with different values of the fetch_lobs or fetch_decimals parameters you will not get different results. This limitation is easily removed for fetch_decimals but not for fetch_lobs due to the fact that the database does not allow this to be changed but requires that the cursor be completely re-executed. If that is truly necessary you will need to add some spaces or use cursor.prepare() with cache_statement set to False to avoid it. It is still on our radar but since the need for this is unusual and there is a workaround it is low priority.

[anthony-tuininga]

This was included in python-oracledb 3.4.0 which was just released.