RE: reference cursors

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 30 Jan 2004 10:50:47 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621033DAE6A@irvmbxw02>

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of=20
> Stephen.Lee_at_DTAG.Com

>=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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 12:50:47 CST