Re: Implementing Table Functions Without Declaring Types at Schema Level

From: Randolf Geist <mahrah_at_web.de>
Date: Thu, 28 May 2009 06:18:13 -0700 (PDT)
Message-ID: <5c986fdb-f251-43d0-a9cf-c7d270a808aa_at_x5g2000yqk.googlegroups.com>



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;

END tablefunction_pkg;
/
show errors

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;

END tablefunction_pkg;
/
show errors

insert into table_a
values (1, 'B', 'C', 2, systimestamp);

insert into table_a
values (2, 'B', 'C', 3, systimestamp);

commit;

select * from table(tablefunction_pkg.tablefunction);

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Thu May 28 2009 - 08:18:13 CDT

Original text of this message