Pass dictionary and return from pl/sql

What is the proper way to pass a dictionary from python to pl/sql anonymous block? Pl/sql need to receive data from dictionary, loop over keys and return a list of keys and counter variable, but I dont understand how.

Code below returns

oracledb.exceptions.NotSupportedError: DPY-3002: Python value of type "dict_items" is not supported

with connection.cursor() as cur:

    # sample dictionary
    d_data = {'111': 'YES', 
              '222': 'NO', 
              '333': 'YES'}

    cur.execute("""\
        DECLARE

        TYPE d_icusnums_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
        d_icusnums d_icusnums_type;
        
        BEGIN
            FOR key_val IN :data LOOP
                d_icusnums('key_val.key') := 'key_val.value';
            END LOOP;

        END;
        """, data=d_data.items())

Looking at the Python OracleDB - Binding PL/SQL Collections documentation:

PL/SQL Collections like Associative Arrays can be bound as IN, OUT, and IN/OUT variables. When binding IN values, an array can be passed directly as shown in this example, which sums up the lengths of all of the strings in the provided array. First the PL/SQL package definition:

create or replace package mypkg as

    type udt_StringList is table of varchar2(100) index by binary_integer;

    function DemoCollectionIn (
        a_Values            udt_StringList
    ) return number;

end; /

create or replace package body mypkg as

    function DemoCollectionIn (
        a_Values            udt_StringList
    ) return number is
        t_ReturnValue       number := 0;
    begin
        for i in 1..a_Values.count loop
            t_ReturnValue := t_ReturnValue + length(a_Values(i));
        end loop;
        return t_ReturnValue;
    end;

end; /

Then the Python code:

values = ["String One", "String Two", "String Three"] 
return_val = cursor.callfunc("mypkg.DemoCollectionIn", int, [values])
print(return_val)        # will print 32

This assumes (as do all the other documentation examples) that the value being passed is a collection with a numeric index where the input is effectively an array and not a dictionary.

Therefore, it appears that passing dictionaries is not supported.

As an alternative, you can convert your dictionary to key-value pairs and pass those as an array of records:

CREATE PACKAGE data_types AS
  TYPE key_value_pair IS RECORD (
    key   VARCHAR2(100),
    value VARCHAR2(100)
  );

  TYPE key_value_list IS TABLE OF key_value_pair INDEX BY binary_integer;
END;
/

Then:

with connection.cursor() as cur:
    # sample dictionary
    d_data = {'111': 'YES', '222': 'NO', '333': 'YES'}

    key_value_pair_type = connection.gettype("DATE_TYPES.KEY_VALUE_PAIR")
    key_value_list = []

    for key, value in d_data.items():
        kvp = key_value_pair_type.newobject()
        kvp.KEY = key
        kvp.VALUE = value
        key_value_list.append(kvp)
      

    cur.execute("""
DECLARE
  TYPE d_icusnums_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  d_icusnums d_icusnums_type;
BEGIN
  FOR key_val IN :data LOOP
    d_icusnums(key_val.key) := key_val.value;
  END LOOP;

  -- do something with d_icusnums
END;
""",
        [key_value_list]
    )