binding collection of object types

[leo-b]

I'd like to call DBMS_AQ.LISTEN from python-oracledb, which needs a table of object types as input.

However, I didn't manage to bind such a type as IN variable for cursor.callproc().

I have created a small example that shows the problem:

CREATE OR REPLACE PACKAGE aq_test
IS
  TYPE agent_list_t IS TABLE OF SYS.AQ$_AGENT
    INDEX BY binary_integer;
  PROCEDURE list_test (l IN agent_list_t);
END aq_test;
/

CREATE OR REPLACE PACKAGE BODY aq_test
IS
  PROCEDURE list_test (l IN agent_list_t)
  AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(l.COUNT);
  END list_test;
END aq_test;
/

I tried to bind the IN parameters like this:

oracledb.init_oracle_client(config_dir='/etc')
dbc = oracledb.connect(**dbconf)

agent_type = dbc.gettype('SYS.AQ$_AGENT')
a_obj = agent_type.newobject()
a.NAME = 'test_agent'
a.ADDRESS = 'test_queue'
agent_type_list = [ a ]

c = dbc.cursor()
c.callproc('AQ_TEST.LIST_TEST', [agent_type_list])

This yields oracledb.exceptions.DatabaseError: DPI-1013: not supported on python-oracledb and a segfault on cx_Oracle 8.2.

Did I do something wrong?
Is there any workaround?

[anthony-tuininga]

Yes, you can't pass a list of objects directly. That can only be done with certain types (number, string, date). You have to instantiate the agent_list_t object and populate it. Did you need an example of how to do that?

[anthony-tuininga]

Are you using 11g for both client and database? There are definitely some limitations with 11g and objects!

[leo-b]

Are you using 11g for both client and database? There are definitely some limitations with 11g and objects!

Client is 12c, database is 11g. 😟
Looks like I have to interpolate input data in PL/SQL and construct the objects at the database side to work around these limitations. Or pass a list of strings and split them into objects in PL/SQL?

[leo-b]

Using a VARRAY as IN parameter does not work either:

CREATE OR REPLACE TYPE AGENTS_V_T AS VARRAY(20) OF SYS.DBMS_AQ.AQ$_AGENT_T;

>>> agents_type = dbc.gettype('AGENTS_V_T')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/oracledb-1.1.1-py3.9-linux-x86_64.egg/oracledb/connection.py", line 440, in gettype
    obj_type_impl = self._impl.get_type(name)
  File "src/oracledb/impl/thick/connection.pyx", line 524, in oracledb.thick_impl.ThickConnImpl.get_type
  File "src/oracledb/impl/thick/utils.pyx", line 410, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 400, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-24372: invalid object for describe

[anthony-tuininga]

Which version of 12c on the client? You can use a 19c client to access an 11g database and if I recall correctly, the limitations were on the client, not necessarily the server. Can you upgrade your client to 19c and try again?

You can't create a varray of a PL/SQL type -- which explains why that attempt failed!

[leo-b]

Which version of 12c on the client?

12.2.0.1.0

You can use a 19c client to access an 11g database and if I recall correctly, the limitations were on the client, not necessarily the server. Can you upgrade your client to 19c and try again?

OK. I'll try that later, once I can find some time. For now I worked around the problem by interpolating input data in PL/SQL.

Thanks for your help!
I'll post a follow up when I get the chance to test with a 19c client.