RE: reference cursors
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 10:50:47 -0800
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of=20
> Stephen.Lee@xxxxxxxx
>=20
>=20
> ... In more detail (because I'm playing around with this stuff): I
> created a row type, then created a table type, then create a=20
> variable of
> that table type; then do a bulk select into the table type=20
> variable. Now I
> wish to hell there was a way to pass a pointer to that table=20
> type variable
> as the refcursor, but I couldn't figure out a way.
I know Jonathan Lewis had some other suggestions. I am still going to =
add my two cents.
Maybe I don't understand what you're trying to do, but is this what =
you're looking for?
drop table t1 ;
drop table t2 ;
drop type t_tab_type ;
drop type t_type ;
drop package my_types ;
drop function f ;
create table t1 (n number, v varchar2 (30)) ;
create table t2 (n number, v varchar2 (30)) ;
insert into t1 (n, v) values (1, 'ONE') ;
insert into t2 (n, v) values (2, 'TWO') ;
insert into t2 (n, v) values (3, 'THREE') ;
commit ;
create type t_type
as object
(value number,
name varchar2 (30)
) ;
/
create type t_tab_type
as table of t_type ;
/
create package my_types
as
type c_ref is ref cursor ;
end my_types ;
/
create function f (table_num_in in number)
return my_types.c_ref
is
t_data t_tab_type :=3D t_tab_type () ;
rc my_types.c_ref ;
begin
if table_num_in =3D 1
then
for t_rec in (select n, v from t1)
loop
t_data.extend ;
t_data (t_data.count) :=3D t_type (t_rec.n, t_rec.v) ;
end loop ;
elsif table_num_in =3D 2
then
for t_rec in (select n, v from t2)
loop
t_data.extend ;
t_data (t_data.count) :=3D t_type (t_rec.n, t_rec.v) ;
end loop ;
end if ;
open rc for
select
value, name
from=20
table (cast (t_data as t_tab_type)) ;
return rc ;
end f ;
/
SQL> variable x refcursor
SQL> execute :x :=3D f (1)
Proc=E9dure PL/SQL termin=E9e avec succ=E8s.
SQL> print x
VALUE NAME
--------- ------------------------------
1 ONE
SQL> execute :x :=3D f(2)
Proc=E9dure PL/SQL termin=E9e avec succ=E8s.
SQL> print x
VALUE NAME
--------- ------------------------------
2 TWO
3 THREE
SQL>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
