Send a rowtype variable to a procedure using oracledb

Question

I have oracle package ETL_RUN, in the package there is a procedure which is declared like this:

procedure pRunTask(pTask in out nocopy TASK_RUN%rowtype)

I am trying to call the procedure from Python code c using oracledb library:

cursor.callproc('ETL_RUN.pRunTask', [task_run])

But I get an error:

  File "...flows\migration_simple.py", line 48, in init
    cursor.callproc('ETL_RUN.pRunTask', keyword_parameters={'pTask': task_run})
  File "...venv\lib\site-packages\oracledb\cursor.py", line 651, in callproc
    self._call(name, parameters, keyword_parameters)
  File "...venv\lib\site-packages\oracledb\cursor.py", line 113, in _call
    return self.execute(statement, bind_values)
  File "...venv\lib\site-packages\oracledb\cursor.py", line 701, in execute
    impl.execute(self)
  File "src\\oracledb\\impl/thin/cursor.pyx", line 178, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src\\oracledb\\impl/thin/protocol.pyx", line 437, in oracledb.thin_impl.Protocol._process_single_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 438, in oracledb.thin_impl.Protocol._process_single_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 399, in oracledb.thin_impl.Protocol._process_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 376, in oracledb.thin_impl.Protocol._process_message
  File "src\\oracledb\\impl/thin/messages.pyx", line 314, in oracledb.thin_impl.Message.send
  File "src\\oracledb\\impl/thin/messages.pyx", line 2162, in oracledb.thin_impl.ExecuteMessage._write_message
  File "src\\oracledb\\impl/thin/messages.pyx", line 2096, in oracledb.thin_impl.ExecuteMessage._write_execute_message
  File "src\\oracledb\\impl/thin/messages.pyx", line 428, in oracledb.thin_impl.MessageWithData._write_bind_params
  File "src\\oracledb\\impl/thin/messages.pyx", line 1115, in oracledb.thin_impl.MessageWithData._write_bind_params_row
  File "src\\oracledb\\impl/thin/messages.pyx", line 1081, in oracledb.thin_impl.MessageWithData._write_bind_params_column
  File "src\\oracledb\\impl/thin/packet.pyx", line 809, in oracledb.thin_impl.WriteBuffer.write_dbobject
TypeError: object of type 'NoneType' has no len()

I previously got the task_run <oracledb.DbObject MAP.TASK_RUN%ROWTYPE at 0x152bec935e0> variable from another oracle function.

What am I doing wrong?

Answer 1

Here's a working example that uses Oracle's sample schema LOCATION table:

import getpass
import os
import platform

import oracledb

if os.environ.get('DRIVER_MODE') == 'thick':
    ld = None  # On Linux, pass None
    if platform.system() == 'Darwin' and platform.machine() == 'arm64':
        ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_23_3'
    elif platform.system() == 'Darwin' and platform.machine() == 'x86_64':
        ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_19_16'
    elif platform.system() == 'Windows':
        ld = r'C:\oracle\instantclient_19_23'
    oracledb.init_oracle_client(lib_dir=ld)
    print('Using Thick mode')


un = 'cj'
cs = 'localhost/orclpdb1'
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

# Define a function to pretty-print the contents of an Oracle object
# See https://github.com/oracle/python-oracledb/blob/main/samples/object_dump.py
def dump_object(obj, prefix=""):
    if obj.type.iscollection:
        print(f"{prefix}[")
        for value in obj.aslist():
            if isinstance(value, oracledb.DbObject):
                dump_object(value, prefix + "  ")
            else:
                print(f"{prefix}  {repr(value)}")
        print(f"{prefix}]")
    else:
        print(f"{prefix}{{")
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if isinstance(value, oracledb.DbObject):
                print(f"{prefix}  {attr.name}:")
                dump_object(value, prefix + "    ")
            else:
                print(f"{prefix}  {attr.name}: {repr(value)}")
        print(f"{prefix}}}")


connection = oracledb.connect(user=un, password=pw, dsn=cs)

with connection.cursor() as cursor:

    # OUT

    cursor.execute("""
        create or replace function TestFuncOUT return locations%rowtype as
          p locations%rowtype;
        begin
           select * into p from locations where rownum < 2;
           return p;
        end;
        """)
    if cursor.warning:
        print(cursor.warning)

    rt = connection.gettype("LOCATIONS%ROWTYPE")
    r = cursor.callfunc("TESTFUNCOUT", rt)

    print("Fetched row:")
    dump_object(r)

    # IN

    cursor.execute("""
        create or replace procedure TestProcIN(p in locations%rowtype, city out varchar2) as
        begin
            city := p.city;
        end;
    """)
    if cursor.warning:
        print(cursor.warning)

    c = cursor.var(oracledb.DB_TYPE_VARCHAR)
    cursor.callproc("TESTPROCIN", [r, c])
    print("City is:")
    print(c.getvalue())

The output is:

$ python3 t.py
Enter password for cj@localhost/orclpdb1: 
Fetched row:
{
  LOCATION_ID: 1000
  STREET_ADDRESS: '1297 Via Cola di Rie'
  POSTAL_CODE: '00989'
  CITY: 'Roma'
  STATE_PROVINCE: None
  COUNTRY_ID: 'IT'
}
City is:
Roma

You can also locally construct a %ROWTYPE record to be passed into PL/SQL. For example:

    rt = connection.gettype("LOCATIONS%ROWTYPE")
    obj = rt.newobject()
    obj.CITY = 'My Town'
    cursor.callproc("TESTPROCIN", [obj, c])
    print("City is:")
    print(c.getvalue())

(Calling gettype() is "expensive" so retain & reuse its value where possible).