how to return the table type to python

[ptekelly]

So in my oracle db i have two types like this

TYPE GUILD_USERS_DATA_OBJ AS OBJECT (
my_id 					NUMBER, 
name 					VARCHAR2 (100 BYTE), 
api   					VARCHAR2 (100 BYTE));

CREATE OR REPLACE TYPE GUILD_USERS_DATA_TBL AS TABLE OF GUILD_USERS_DATA_OBJ;

Then I have a procedure GET_GUILD_USERS_DATA that takes an ID as IN and the variable to hold the out table type - that returns this table type once called. (it does various selects as expected

in python i have something like this

import oracledb
import os


from dotenv import load_dotenv

load_dotenv()
user = os.getenv('DB_USER')
pwd = os.getenv('DB_PW')
dsn = os.getenv('DB_DSN')


pool = oracledb.create_pool(user=user, password=pwd, dsn=dsn,min=1, max=10, increment=1)
connection = pool.acquire()

cur = connection.cursor()

guild_id = 123456789
user_tab = cur.var(oracledb.CURSOR); 


cur.callproc("GET_GUILD_USERS_DATA",[user_tab,guild_id])

ETC

is this correct? or what is the correct type for table - or for that matter is this even possible in oracledb (thin)
user_tab = cur.var(oracledb.CURSOR);

[anthony-tuininga]

It would be helpful if you showed the definition of the procedure. I assume, however, that the second parameter is an OUT parameter of type GUILD_USERS_DATA_TBL. If so, something like this should get you going:

typ = connection.gettype("GUILD_USERS_DATA_TBL")
user_tab = cur.var(typ)
cur.callproc("GET_GUILD_USERS_DATA", [guild_id, user_tab])
obj = user_tab.getvalue()
for user in obj.aslist():
    print("my_id:", user.MY_ID)

[ptekelly]

Ah - that looks good. Will give it a try thank you