是否可以在包/过程之外创建Oracle关联数组类型? [英] Is it possible to create Oracle associative array type outside of a package/procedure?
问题描述
在Oracle数据库10g中,是否可以在包或过程之外创建关联数组类型?我希望能够做到这一点,以便可以在另一种类型中引用此关联数组类型.例如:
In Oracle Database 10g, is it possible to create an associative array type outside of a package or procedure? I would like to be able to do this so that I can reference this associative array type in another type. For example:
create type my_type_map is table of varchar2(10) index by varchar2(10);
create type my_other_type as object (
id number(15),
member procedure initialize(p_my_type_map my_type_map)
) not instantiable not final;
我得到的错误是:
SQL> create type my_type_map is table of varchar2(20) index by varchar2(10);
2 /
Warning: Type created with compilation errors.
SQL> show errors;
Errors for TYPE MY_TYPE_MAP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00355: use of pl/sql table not allowed in this context
SQL>
Oracle认为:
index by varchar2(10)
是PL/SQL,不允许在创建SQL类型时使用它.如果Oracle确实不允许在程序包外部定义关联数组,那么有没有很好的选择?是否可以创建在包内部扩展Oracle对象的类型,以便在同一包中定义所有类型?
to be PL/SQL and doens't allow it in the creation of SQL types. If Oracle really doesn't allow associative arrays to be defined outside of packages then is there a good alternative? Is it possible to create types that extend Oracle's Object inside of a package so that all types are defined in the same package?
谢谢, 杰夫
更正了代码示例,添加了日志,并添加了可能的替代内容作为问题.
Corrected code sample, added log, added possible alternative as question.
推荐答案
答案是否定的,您不能做您想做的事情,比创建类型向对象添加BOOLEAN类型的变量要多得多. .对象中的项目必须包含Oracle类型,而不是PL/SQL类型.笨拙的替代方法可能是:
The answer is no, you cannot do what you're trying to do, any more than you can create a type to add a BOOLEAN typed variable to an object. The items in an object must contain Oracle types, not PL/SQL types. A bit clunky alternative could be:
CREATE TYPE t_aa AS VARRAY(10) OF VARCHAR2(10);
CREATE OR REPLACE TYPE t_ua AS OBJECT (ID NUMBER(15)
, MEMBER PROCEDURE initialize(p_aa t_aa)
, MEMBER PROCEDURE initialize(p_aa_i t_aa))
NOT INSTANTIABLE NOT FINAL;
将关联的变量对存储在两个VARRAY中.您将必须知道阵列的最大可能大小.
Store your associated pairs of variables in the two VARRAYs. You will have to know the largest possible size of your arrays.
这篇关于是否可以在包/过程之外创建Oracle关联数组类型?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!