cx_Oracle And User Defined Types
Question: Does anyone know an easier way to work with user defined types in Oracle using
cx_Oracle?

For example, if I have these two types:

    
    
    CREATE type my_type as object(
    component   varchar2(30)
    ,key    varchar2(100)
    ,value   varchar2(4000))
    /
    CREATE type my_type_tab as table of my_type
    /
    

And then a procedure in package `my_package` as follows:

    
    
    PROCEDURE my_procedure (param  in  my_type_tab);
    

To execute the procedure in PL/SQL I can do something like this:

    
    
    declare
      l_parms   my_type_tab;
      l_cnt     pls_integer;
    begin
      l_parms := my_type_tab();
      l_parms.extend;
      l_cnt := l_parms.count;
      l_parms(l_cnt) := my_type('foo','bar','hello');
      l_parms.extend;
      l_cnt := l_parms.count;
      l_parms(l_cnt) := my_type('faz','baz','world');
      my_package.my_procedure(l_parms);
    end;
    

However, I was wondering how I can do it in Python, similar to this code:

    
    
    import cx_Oracle
    orcl = cx_Oracle.connect('foo:bar@mydb.com:5555/blah' + instance)
    curs = orcl.cursor()
    params = ???
    curs.execute('begin my_package.my_procedure(:params)', params=params)
    

If the parameter was a string I can do this as above, but since it's an user-
defined type, I have no idea how to call it without resorting to pure PL/SQL
code.

Edit: Sorry, I should have said that I was looking for ways to do more in
Python code instead of PL/SQL.


Answer: While cx_Oracle can select user defined types, it does not to my knowledge
support passing in user defined types as bind variables. So for example the
following will work:

    
    
    cursor.execute("select my_type('foo', 'bar', 'hello') from dual")
    val, = cursor.fetchone()
    print val.COMPONENT, val.KEY, val.VALUE
    

However what you can't do is construct a Python object, pass it in as an input
argument and then have cx_Oracle "translate" the Python object into your
Oracle type. So I would say you're going to have to construct your input
argument within a PL/SQL block.

You can pass in Python lists, so the following should work:

    
    
    components=["foo", "faz"]
    values=["bar", "baz"]
    keys=["hello", "world"]
    cursor.execute("""
    declare
      type udt_StringList is table of varchar2(4000) index by binary_integer;
      l_components udt_StringList := :p_components;
      l_keys udt_StringList := :p_keys;
      l_values udt_StringList := :p_values;
      l_parms my_type_tab;
    begin
      l_parms.extend(l_components.count);
      for i in 1..l_components.count loop
        l_parms(i) := my_type(l_components(i), l_keys(i), l_values(i));
      end loop;
    
      my_package.my_procedure(l_parms);
    end;""", p_components=components, p_values=values, p_keys=keys)
    

