SQL & PL/SQL

We would want to implement Virtual Tables concept.

If we create the Global Types and Functions the solution works well with the data being hard coded.

create or replace type myScalarType as object

( x int,

y date,

z varchar2(25)

)

/

create or replace type myTableType as table of myScalarType;

/

create or replace package body my_pkg

as

procedure get_data( p_inputs in varchar2, p_cursor in out refcur )

is

l_data myTableType := myTableType();

begin

for i in 1 .. 5 loop
l_data.extend;
l_data(l_data.count) := myScalarType( i,
sysdate+i, 'The input ' || p_inputs );
end loop;

open p_cursor for select * from the ( select cast( l_data
as myTableType ) from dual );
end;

end;
/

set autoprint on

variable x refcursor


exec my_pkg.get_data( 'hello world', :x )


PL/SQL procedure successfully completed.

X Y Z

---------- --------- -------------------------

1 05-MAY-00 The input hello world

2 06-MAY-00 The input hello world

3 07-MAY-00 The input hello world

4 08-MAY-00 The input hello world

5 09-MAY-00 The input hello world


The above solution works fine.

We want the dynamic data to be picked up from the Original Tables and create the Types/Functions within the Package itself (not Global). How do we go about doing that.

Can someone please help ????