Tutorial :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.


Solution:1

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)  


Solution:2

Are you trying to populate the table of objects more efficiently?

If you can do a SELECT, have a look at the BULK COLLECT INTO clause


Solution:3

I'm not quite sure what you mean by hard-coded, but you can build a dynamic array like this:

SQL> desc my_procedure  Parameter Type        Mode Default?   --------- ----------- ---- --------   P_IN      MY_TYPE_TAB IN       SQL> declare    2     l_tab my_type_tab;    3  begin    4     select my_type(owner, table_name, column_name)    5       bulk collect into l_tab    6       from all_tab_columns    7      where rownum <= 10;    8     my_procedure (l_tab);    9  end;   10  /    PL/SQL procedure successfully completed  

This has been tested with Oracle 11.1.0.6.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »