cx_Oracle 和用户定义类型
有谁知道使用 cx_Oracle 在 Oracle 中处理用户定义类型的更简单方法?
例如,如果我有这两种类型:
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
中的过程如下:
PROCEDURE my_procedure (param in my_type_tab);
要在 PL/SQL 中执行该过程,我可以这样做:
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 中执行此操作,类似于此代码:
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 代码的情况下调用它。
编辑:抱歉,我应该说我正在寻找用 Python 代码而不是 PL/SQL 做更多事情的方法。
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:[email protected]: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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
虽然 cx_Oracle 可以选择用户定义类型,但据我所知,它不支持将用户定义类型作为绑定变量传递。 例如,以下内容将起作用:
但是您不能做的是构造一个 Python 对象,将其作为输入参数传递,然后让 cx_Oracle 将 Python 对象“翻译”为您的 Oracle 类型。 所以我想说你必须在 PL/SQL 块中构造你的输入参数。
您可以传入 Python 列表,因此以下内容应该有效:
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:
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:
您是否想更有效地填充对象表?
如果您可以执行 SELECT,请查看 BULK COLLECT INTO 子句
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
我不太清楚你所说的硬编码是什么意思,但你可以像这样构建一个动态数组:
这已经用 Oracle 11.1.0.6 进行了测试。
I'm not quite sure what you mean by hard-coded, but you can build a dynamic array like this:
This has been tested with Oracle 11.1.0.6.