cx_Oracle和用户定义的类型

问题描述

有人知道使用cx_Oracle在Oracle中使用用户定义类型的更简单方法吗?

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
/

然后是软件包my_package中的一个过程，如下所示:

And then a procedure in package my_package as follows:

PROCEDURE my_procedure (param  in  my_type_tab);

要在PL/SQL中执行该过程，我可以执行以下操作:

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;

但是，我想知道如何在Python中做到这一点，类似于以下代码:

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

import cx_Oracle
orcl = cx_Oracle.connect('foo:[email protected]:5555/blah' + instance)
curs = orcl.cursor()
params = ???
curs.execute('begin my_package.my_procedure(:params)', params=params)

如果参数是字符串，我可以按照上面的方法进行操作，但是由于它是用户定义的类型，因此我不知道如何在不求助于纯PL/SQL代码的情况下调用它.

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.

对不起，我应该说我在寻找用Python代码而不是PL/SQL做更多事情的方法.

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

推荐答案

尽管cx_Oracle可以选择用户定义的类型，但据我所知，它不支持将用户定义的类型作为绑定变量进行传递.因此，例如，以下方法将起作用:

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

但是，您不能做的是构造一个Python对象，将其作为输入参数传递，然后让cx_Oracle将Python对象转换"为您的Oracle类型.因此，我想您将不得不在PL/SQL块中构造输入参数.

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.

您可以传递Python列表，因此以下各项应适用:

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)

这篇关于cx_Oracle和用户定义的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！