Implementing Table Functions Without Declaring Types at Schema Level

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?

Thanks.

drop table TABLE_A;
drop table TABLE_B;
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 table TABLE_B
(

  COL_BA INTEGER not null,
  COL_BB VARCHAR2(50),
  COL_BC INTEGER not null,
  COL_BD VARCHAR2(1000) 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

	TYPE r IS RECORD
	(
	       x          INTEGER,
	       y          VARCHAR2(2000)
	);

	TYPE rtab IS TABLE OF r;

	FUNCTION tablefunction
	RETURN rtab PIPELINED
	IS

	  CURSOR my_cur IS
	  SELECT *
	  FROM   table_a;

	BEGIN

		For cur in my_cur
		LOOP

		  PIPE ROW (r(cur.col_aa, cur.col_ab));


		END LOOP;

		RETURN;

	END tablefunction;

END tablefunction_pkg;
/

show errors Received on Wed May 27 2009 - 06:32:25 CDT