无法在 oracle 中创建表函数，在 FETCH 游标和 INTO 变量之间发现类型不匹配

Question

我正在尝试创建一个表函数以在tableau的自定义SQL中使用，但是我收到错误，在FETCH光标和INTO变量之间发现类型不匹配。下面是我正在尝试的代码，我已经创建了一个类型对象和该类型对象的表。函数 my_fct 应返回带有 select 语句输出的表。

CREATE
OR replace type DATA_OBJ AS OBJECT (
id varchar2(10)
);  
CREATE
OR replace type 
DATA_OBJ_TAB AS TABLE OF DATA_OBJ;

CREATE OR REPLACE FUNCTION my_fct()
RETURN DATA_OBJ_TAB PIPELINED 
AS
TYPE CurTyp IS REF CURSOR RETURN DATA_OBJ_TAB%ROWTYPE;
rc CurTyp;
CURSOR data IS SELECT ID from alumni_data;
BEGIN
FOR rc IN data LOOP
PIPE ROW (rc);
END LOOP;
END;

Answer 1

这可以使用打包的 PTF 来实现，而无需使用 SQL 数据类型。

像这样：

create table alumni_data (id, memo) as
select rownum id, 'memo '||rownum from dual connect by level<=3
/
create or replace package pack as
type arrT is table of alumni_data%rowtype;
function get (c varchar2) return arrT pipelined;  
end;
/
create or replace package body pack as
function get (c varchar2) return arrT pipelined is
arr arrT; 
begin 
select * bulk collect into arr
from alumni_data
where memo like c||'%';
for i in 1..arr.count loop
pipe row (arr(i));  
end loop;
return;
end;
end;
/

结果：

select * from pack.get ('mem');
ID MEMO                                         
---------- ---------------------------------------------
1 memo 1                                       
2 memo 2                                       
3 memo 3                                       

Answer 2

请看以下示例：

SQL> create or replace type data_obj as object
2    (id varchar2(10));
3  /
Type created.
SQL> create or replace type
2    data_obj_tab as table of data_obj;
3  /
Type created.
SQL> create or replace function my_fct
2    return data_obj_tab pipelined
3  as
4    l_vc data_obj := data_obj(null);
5  begin
6    for cur_r in (select id from alumni_data) loop
7      l_vc.id := cur_r.id;
8      pipe row (l_vc);
9    end loop;
10    return;
11  end;
12  /
Function created.
SQL> select * from table(my_fct);
ID
----------
CLARK
KING
MILLER
SQL>