RE: reference cursors

> -----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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: