In article <1mME4.3051$k5.62992_at_news1.frmt1.sfba.home.com>,
"Hans Soelaeman" <hanschan_at_home.com> wrote:
> I am using a lot of stored procedures / functions for encapsulating
the
> database structure; And, I am thinking to migrate to Oracle. In
PostgreSQL I
> am able to do:
>
> create function test() returns setof text
> as 'select ''Col1:'' || col1 from atable'
> language 'sql';
>
> And calling
> select test();
>
> Will results:
> Col1: content1
> Col2: content2
> Col3: content3
>
> 3 Rows
>
> I am wondering how can I do the same in Oracle PL/SQL?
> Such calling
>
> select test() from dual;
>
> can produce multiple values / a recordset back.
>
> Thanks.
>
> Hans.
>
>
There are ref cursors which may apply. As well, you can use object
types to synthesize result sets if need be. For example:
ops$tkyte_at_8i> create or replace type myScalarType as object( id number,
dataum varchar2(30) )
2 /
Type created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace type myTableType as table of
myScalarType;
2 /
Type created.
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace function get_data(
p_how_many_do_you_want in number ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 begin
5 for i in 1 .. p_how_many_do_you_want
6 loop
7 l_data.extend;
8 l_data(l_data.count) := myScalarType( i, 'row ' ||
i );
9 end loop;
10
11 return l_data;
12 end;
13 /
Function created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
2 from THE ( select cast( get_data(5) as mytableType ) from dual )
a
3 /
ID DATAUM
---------- ------------------------------
1 row 1
2 row 2
3 row 3
4 row 4
5 row 5
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Mar 30 2000 - 12:15:37 CST
