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.