ORA-22922 in async LOB reading from cursor

[klymenkosergiy]

1. What versions are you using?
   Python 3.9.13 (Windows, Oracle Linux 8)
   Database: Oracle 19c
   oracledb: 3.1.0, 3.1.1

2. Is it an error or a hang or a crash?
   DatabaseError with ORA-22922

3. What error(s) or behavior you are seeing?
   I have observed the error "ORA-22922: nonexistent LOB value" after reading around 100 LOB objects. If lobAutoload is configured (refCursor.outputtypehandler = _connection_lobautoload_handler) I fetched the async cursor well.

I noticed that the issue is present only for the async version in oracledb module, sync version works well, my old code based on cx_Oracle + cx_Oracle_async also works well with this case.

4. Does your application call init_oracle_client()?
   no

5. Include a runnable Python script that shows the problem.

create or replace package pkg_test1 is

function encode(p_prefix nvarchar2, p_idx number) return blob;

function test return sys_refcursor;

end pkg_test1;
/
create or replace package body pkg_test1 is

gServerCodepage v$nls_parameters.value%type;

function encode(p_prefix nvarchar2, p_idx number) return blob
as
l_value nvarchar2(2000);
l_blob blob;
l_raw long raw;
begin
  l_value := p_prefix || '-' || to_char(p_idx);
  
  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
  
  if gServerCodepage != 'AL32UTF8' then
    l_raw := utl_raw.cast_to_raw(convert(l_value, 'AL32UTF8', gServerCodepage));
  else
    l_raw := utl_raw.cast_to_raw(l_value);
  end if;
  
  dbms_lob.writeappend(l_blob, utl_raw.length(l_raw), l_raw);
  return l_blob;
end encode;  

function test return sys_refcursor
as
l_cur sys_refcursor;
begin
  open l_cur for
    SELECT rownum                        id,
           pkg_test1.encode('col1', rownum) blob1,
           pkg_test1.encode('col2', rownum) blob2,
           pkg_test1.encode('col3', rownum) blob3,
           pkg_test1.encode('col4', rownum) blob4,
           pkg_test1.encode('col5', rownum) blob5,
           pkg_test1.encode('col6', rownum) blob6,
           pkg_test1.encode('col7', rownum) blob7,
           pkg_test1.encode('col8', rownum) blob8,
           pkg_test1.encode('col9', rownum) blob9,
           pkg_test1.encode('col10', rownum) blob10,
           pkg_test1.encode('col11', rownum) blob11,
           pkg_test1.encode('col12', rownum) blob12                                                                                        
    FROM dual
    --CONNECT BY LEVEL <= 1000;    
    CONNECT BY LEVEL <= 25;    
 return l_cur;   
end test;

begin
  select 
   (select p.value from v$nls_parameters p where p.PARAMETER = 'NLS_NCHAR_CHARACTERSET')
   into gServerCodepage
  from dual; 
end pkg_test1;
/


import oracledb
import asyncio

def _connection_lobautoload_handler(cursor, metadata):
	""" Handler to configure cursor types in connection """
	if metadata.type_code is oracledb.DB_TYPE_CLOB:
		return cursor.var(oracledb.DB_TYPE_LONG, arraysize=max(cursor.arraysize, 4096))
	if metadata.type_code is oracledb.DB_TYPE_BLOB:
		return cursor.var(oracledb.DB_TYPE_LONG_RAW, arraysize=max(cursor.arraysize, 4096))
	if metadata.type_code is oracledb.DB_TYPE_NCLOB:
		return cursor.var(oracledb.DB_TYPE_LONG_NVARCHAR, arraysize=max(cursor.arraysize, 4096))

def main():
	pool = oracledb.create_pool(
			user = "user",
			password ="password",
			dsn = "connection-string"
			)
	con = pool.acquire()
	try:
		with con.cursor() as cur, con.cursor() as refCursor:
			cur.execute(
					""" begin :cur := pkg_test1.test; end;""",
					cur = refCursor)

			rowCount = 0
			while True:
				row = refCursor.fetchone()
				if row == None: break
				for idx in range(0, len(refCursor.description)):
					if refCursor.description[idx][0].startswith('BLOB'):
						value = (row[idx].read()).decode('utf-8') if row[idx] != None and isinstance(row[idx], oracledb.LOB) else row[idx]
						print(f'{rowCount}: {refCursor.description[idx][0]}={value}')
				rowCount += 1
	finally:
		con.close()

async def mainAsync():
	pool = oracledb.create_pool_async(
			user = "user",
			password ="password",
			dsn = "connection-string"
			)
	con = await pool.acquire()
	try:
		async with con.cursor() as cur, con.cursor() as refCursor:
			#refCursor.outputtypehandler = _connection_lobautoload_handler
			await cur.execute(
					""" begin :cur := pkg_test1.test; end;""",
					cur = refCursor)

			rowCount = 0
			while True:
				row = await refCursor.fetchone()
				if row == None: break
				for idx in range(0, len(refCursor.description)):
					if refCursor.description[idx][0].startswith('BLOB'):
						value = (await row[idx].read()).decode('utf-8') if row[idx] != None and isinstance(row[idx], oracledb.AsyncLOB) else row[idx]
						print(f'{rowCount}: {refCursor.description[idx][0]}={value}')
				rowCount += 1
	finally:
		await con.close()

if __name__ == '__main__':
	print ('Test case#1: oracledb (sync mode).')
	main()
	print ('Test case#2: oracledb (async mode).')
	asyncio.run(mainAsync())

[anthony-tuininga]

I tried your test with and without the type handler and did not have any difficulties. This was on Linux with a 23ai database. I can ask internally for someone to try with 19c on Windows.

Note that python-oracledb does have the oracledb.fetch_lobs attribute which you can set to False which will effectively do the same as your output type handler, just internally.

[klymenkosergiy]

I have tested on two other instances of Oracle 19 and on one instance of Oracle 21, and I reproduced the error in all my tests. I noticed that the count of successfully read rows was different, but the fetch was terminated with ORA-22922 anyway. Did you run the test-case with 25 rows in dataset or the tests with both variants (25 and 1000 rows) pass on Oracle 23?

[anthony-tuininga]

I ran both scenarios with 23ai and was unable to replicate. Someone else internally ran with 19c and was able to replicate. I will do some more investigation. If you are able to confirm that 23ai works for you, that would be appreciated.

[klymenkosergiy]

Unfortunately, I don’t have an Oracle 23 instance available to reproduce the issue.

[anthony-tuininga]

I was able to get access to a 19c database myself and can confirm the problem exists there. The workaround, however, is very straightforward:

oracledb.defaults.fetch_lobs = False

This also dramatically increases the performance! I suspect something to do with lifetime of the LOB -- since asyncio handles such things considerably differently -- but I will look into it further. I would strongly recommend avoiding its use even if this problem is resolved, however!

[anthony-tuininga]

I discovered the source of the issue and why 23ai database succeeds whereas 19c database fails. Responses from the database are sent in packets of SDU size (default is 8,192 bytes). If a response requires more than one packet the client does not know when the response is complete except by parsing the response. For synchronous mode this is not a problem as when the end of a packet is reached without receiving the entire response the client waits until the next packet arrives and continues parsing. For asynchronous mode packets are received asynchronously and a parse is attempted. If not all of the packets have been received, the attempt is discarded and another attempt is made when more packets have been received. (If you're wondering why the parsing functions are not asynchronous themselves, that is explained by the fact that database responses may consist of many thousands of component parts, each of which may be split across a packet boundary and using asynchronous functions would dramatically reduce the performance of the driver). With Oracle Database 23ai, the database sends a flag with each packet indicating if this packet is the final packet in the response. The asynchronous client is then able to make use of that and avoid the reparsing. The logic that currently runs for reparsing is creating the LOB objects and then destroying them during the subsequent reparse needed. If you ensure that the response fits within a single packet the "problem" goes away (in your scenario, setting arraysize to 10 does this).

TLDR; I hope to have a patch for this issue shortly. Nevertheless, avoid LOBS for improved performance anyway! :-)

[cjbj]

The real summary is that this is all more efficient with Oracle Database 23ai, so you should looking at upgrading when possible!

[anthony-tuininga]

I have pushed a patch that should correct this issue and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you can test your scenario and confirm the patch works as expected, that would be appreciated!