具有批量集合的表函数抛出无效的数据类型 [英] Table function with bulk collection throws invalid datatype
问题描述
我正在编写一个函数，我想用表函数包装它，以便可以与select查询一起使用.
I am writing a function and i want to wrap it with table function so i can use with select query.
这是我的类型声明和函数的某些行
Here is my type declaration and the some lines of my functions
CREATE OR REPLACE PACKAGE TYPES
AS
TYPE CURSORTYPE IS REF CURSOR;
TYPE vbugsrec
IS
RECORD (
bug_id bugs.bug_id%TYPE,
facility bugs.facility%TYPE
);
TYPE vbugstable
IS
TABLE OF vbugsrec
INDEX BY BINARY_INTEGER;
END;
/
CREATE OR REPLACE PACKAGE BODY CustomQueries
AS
FUNCTION pendverifylist (myldapid IN userpass.ldapalias%TYPE,
maxrows IN PLS_INTEGER:= CustomQueries.maxrecords)
RETURN types.vbugstable
IS
datarows types.vbugstable;
var_useralias userpass.ldapalias%TYPE
:= UPPER (pendverifylist.myldapid) ;
CURSOR pendverify_cur (
cursor_var_alias IN userpass.ldapalias%TYPE,
cursor_var_mybugstatus IN bugs.bug_status%TYPE,
cursor_var_wild IN qa_list.component%TYPE
)
IS
SELECT buglist.bug_id, buglist.facility
FROM bugs buglist,
(SELECT qa.product, qa.component
FROM qa_list qa, userpass UP
WHERE qa.qa_id = UP.userid
AND UP.ldapalias = cursor_var_alias) plist
WHERE buglist.bug_status = cursor_var_mybugstatus
AND buglist.smr_state IN (SELECT fs.finalstate
FROM finalstates fs)
AND buglist.facility = plist.product
AND (buglist.product LIKE plist.component
OR plist.component = cursor_var_wild);
BEGIN
OPEN pendverifylist.pendverify_cur (cursor_var_alias => pendverifylist.var_useralias,
cursor_var_mybugstatus => CustomQueries.default_bugstatus,
cursor_var_wild => CustomQueries.wildcard);
FETCH pendverifylist.pendverify_cur
BULK COLLECT INTO pendverifylist.datarows
LIMIT LEAST (GREATEST (0, pendverifylist.maxrows),
CustomQueries.MAXRECORDS);
CLOSE pendverifylist.pendverify_cur;
RETURN pendverifylist.datarows;
END pendverifylist;
END CustomQueries;
/
当我想使用如下所示的TABLE函数时，出现错误.ORA-00902:无效的数据类型
When i want to use TABLE function like below, i get error.ORA-00902: invalid datatype
SELECT * FROM TABLE(CUSTOMQUERIES.PENDVERIFYLIST ( 'product', 50 ));
有人可以帮我在这里做错什么吗?
Can anyone please help what i am doing wrong here?
预先感谢
推荐答案
您正在尝试在纯SQL中使用包级类型，这是不允许的.包中声明的类型在PL/SQL外部(甚至在PL/SQL内的普通SQL语句中)不可见或无效.您正在做的事情的简化版本:
You're trying to use package-level types in plain SQL, which isn't allowed. The types declared in the package are not visible to or valid outside PL/SQL (or even in plain SQL statements within PL/SQL). A cut-down version of what you're doing:
create or replace package types as
type my_rec_type is record (dummy dual.dummy%type);
type my_table_type is table of my_rec_type index by binary_integer;
end types;
/
create or replace package p42 as
function get_table return types.my_table_type;
end p42;
/
create or replace package body p42 as
function get_table return types.my_table_type is
my_table types.my_table_type;
begin
select * bulk collect into my_table from dual;
return my_table;
end get_table;
end p42;
/
select * from table(p42.get_table);
SQL Error: ORA-00902: invalid datatype
即使在软件包中，如果您有尝试使用表函数的过程，也会出错.如果您添加了:
Even within the package, if you had a procedure that tried to use the table function it would error. If you added:
procedure test_proc is
begin
for r in (select * from table(get_table)) loop
null;
end loop;
end test_proc;
...程序包主体编译将失败，并显示ORA-22905: cannot access rows from a non-nested table item.
... the package body compilation would fail with ORA-22905: cannot access rows from a non-nested table item.
You need to declare the types at schema level, not in a package, so using the SQL create type command:
create type my_obj_type is object (dummy varchar2(1));
/
create type my_table_type is table of my_obj_type;
/
create or replace package p42 as
function get_table return my_table_type;
end p42;
/
create or replace package body p42 as
function get_table return my_table_type is
my_table my_table_type;
begin
select my_obj_type(dummy) bulk collect into my_table from dual;
return my_table;
end get_table;
end p42;
/
select * from table(p42.get_table);
DUMMY
-----
X
这篇关于具有批量集合的表函数抛出无效的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
