CODEWITHSUNDEEP
oracle-databasefunctionplsqltypes
ZZ3111
ZZ3111

Reputation: 87

Unable to create table function in oracle, type mismatch found between FETCH cursor and INTO variable

I am trying to create a table function to use in tableau's custom SQL, but I am getting an error, type mismatch found between FETCH cursor and INTO variable. Below is the code I am trying, I have created a type object and table of that type object. Function my_fct should return the table with a select statement output. 

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;

Upvotes: 1

Views: 132

Answers (2)

0xdb
0xdb

Reputation: 3697

This can be implemented with a packaged PTF without using the SQL data types at all.

Something like this:

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;
/

Result:

select * from pack.get ('mem');

        ID MEMO                                         
---------- ---------------------------------------------
         1 memo 1                                       
         2 memo 2                                       
         3 memo 3

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

Have a look at the following example:

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>

Upvotes: 1

Related Questions