Return type as table not working with Oracle function-CodePudding

I have following package and function structure , need to understand if it is possible.

If yes - then please help me understand - where I am going wrong.

Package -->

CREATE OR REPLACE PACKAGE MY_PACKAGE AS

    TYPE MY_EVENTS IS TABLE OF MY_TABLE%ROWTYPE INDEX BY PLS_INTEGER  ;
    FUNCTION MY_FUNCTION RETURN MY_EVENTS;

END MY_PACKAGE ;

Package body -->

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE    AS

FUNCTION MY_FUNCTION  RETURN   MY_EVENTS  IS

EVENT_RECORDS MY_EVENTS;


BEGIN

select MT.* BULK COLLECT INTO EVENT_RECORDS from MY_TABLE MT ;

/*
some processing  on EVENT_RECORDS 
*/


  RETURN EVENT_RECORDS ;


END MY_FUNCTION;
END MY_PACKAGE ;

The way I am trying to call this function -->

select MY_PACKAGE.MY_FUNCTION from dual ;

It gives error -->

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error at Line: 13 Column: 8

Thank you for your help in Advance !.

CodePudding user response：

If you want to return that type at SQL level, then you'll have to declare it (the type) at SQL level, not within the package.

It would work the way you did it if you called MY_FUNCTION from PL/SQL, though.

Here's an example of how you might do that:

Types:

SQL> create or replace type t_dept_row as object
  2    (deptno number,
  3     dname  varchar2(20),
  4     loc    varchar2(20));
  5  /

Type created.

SQL> create or replace type t_dept_tab as table of t_dept_row;
  2  /

Type created.

Package:

SQL> create or replace package my_package as
  2    function my_function return t_dept_tab;
  3  end;
  4  /

Package created.

SQL> create or replace package body my_package as
  2    function my_function return t_dept_tab
  3    is
  4      l_tab t_dept_tab;
  5    begin
  6      select t_dept_row(deptno, dname, loc)
  7      bulk collect into l_tab
  8      from dept;
  9
 10      return l_tab;
 11    end;
 12  end;
 13  /

Package body created.

Testing:

SQL> select * From table(my_package.my_function);

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

SQL>