Re: Implementing Table Functions Without Declaring Types at Schema Level
Date: Thu, 28 May 2009 06:18:13 -0700 (PDT)
On May 27, 1:32 pm, KevinS <Sear..._at_googlemail.com> wrote:
> All of the examples for table functions I have come across use an
> object type declared at schema level.
> Is it possible to use table functions within a package using types
> declared in pl/sql only.
> My attempts have drawn blanks, possibly due to misunderstanding/misuse
> of Types.
> The code I have used is below.
> Can anyon advise?
Yes, it is possible. The implementation however that Oracle uses under the covers is odd, at least up to 10.2, because it will simply "silently" generate SQL "types" for your PL/SQL types to support the table function.
You can identify those by checking for types in the data dictionary that start with SYS_PLSQL. The number following this prefix corresponds to the object id of the package where the types are defined.
If you drop the package, these types are supposed to get dropped, too.
As with all these "odd" implementations I've seen some issues with these, e.g. it used to be in 10.2.0.2 the case that if you exported a schema containing such system generated types and imported it back into the same database but a different schema that Oracle lost track of these system generated types, leading to all kind of odd behaviour, like internal errors when re-compiling the packages. After dropping the types manually and re-compiling the packages everything was fine again. I don't know if this has been addressed in later 10.2 point releases.
In 11.1 I noticed that these system generated types are no longer visible, but I don't know if these are simply not shown any longer in the dictionary or if the implementation has changed significantly.
Regarding your sample code: It doesn't use the PL/SQL record correctly and doesn't compile for me.
Using something like this should work:
drop table TABLE_A;
drop package tablefunction_pkg;
create table TABLE_A
COL_AA INTEGER not null, COL_AB VARCHAR2(2000) not null, COL_AC VARCHAR2(30), COL_AD INTEGER not null, COL_AE TIMESTAMP(6) not null
CREATE OR REPLACE PACKAGE tablefunction_pkg is
TYPE r IS RECORD ( x INTEGER, y VARCHAR2(2000) ); TYPE rtab IS TABLE OF r; FUNCTION tablefunction RETURN rtab PIPELINED;
CREATE OR REPLACE PACKAGE BODY tablefunction_pkg is
FUNCTION tablefunction RETURN rtab PIPELINED IS CURSOR my_cur IS SELECT * FROM table_a; a_row r; BEGIN For cur in my_cur LOOP a_row.x := cur.col_aa; a_row.y := cur.col_ab; PIPE ROW (a_row); END LOOP; RETURN; END tablefunction;
insert into table_a
values (1, 'B', 'C', 2, systimestamp);
insert into table_a
values (2, 'B', 'C', 3, systimestamp);
select * from table(tablefunction_pkg.tablefunction);
Oracle related stuff blog: