Re: REF CURSOR

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 18 Nov 1999 09:16:51 -0500
Message-ID: <KQo0OBh8P7RLPeRrB=Tv6jc41BdH@4ax.com>

A copy of this was sent to jeperkins4 <johnp_at_1rent.com> (if that email address didn't require changing) On Thu, 18 Nov 1999 08:35:45 GMT, you wrote:

>Is there an easy way to get a result set in one procedure/function that
>calls another procedure/function that returns a reference cursor?
>
>function getUser
> (p_UserID in integer)
> return types.cursorType is
>
> l_cursor types.cursorType;
> begin
> open l_cursor for
> select
>a.UserID,UserName,UserPassword,FirstName,LastName,SSN,DateOfBirth,
>
>DriversLicense,LicenseStateID,Email,CompanyName,HasWebsite,MustChangePas
>sword,
> TermsAccepted,a.LastUpdated
> from common.users a, common.userdetails b
> where a.userid = b.userid(+)
> and a.UserID = p_UserID;
>
> return l_cursor;
> end;
>
>How would I loop through the return values of getUser(2)?

It would look like one of the three loops in showUser below. I've used three different methods of fetching (into a record based on a rowtype, into 3 scalars, into a user defined record)

tkyte_at_8.0> create or replace package types   2 as
  3 type cursorType is ref cursor;   4

  4      type someRec is record
  5      (    username    varchar2(30),
  6          user_id      number,
  7          created      date
  8      );

  9 end;
 10 /

Package created.

tkyte_at_8.0> show errors
No errors.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace function getUser( p_userid in integer ) return

types.cursorType
  2 is
  3 l_cursor types.cursorType;
  4 begin
  5 open l_cursor for select * from all_users where user_id = p_userid;   6
  6 return l_cursor;
  7 end;
  8 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace procedure showUser
  2  as
  3      l_cursor    types.cursorType;
  4  
  4      l_rec1       all_users%rowtype;
  5  
  5      l_username   varchar2(30);
  6      l_user_id    number;
  7      l_created    date;
  8  
  8      l_rec2       types.someRec;

  9 begin
 10
 10

 10      l_cursor := getUser( UID );
 11      loop
 12          fetch l_cursor into l_rec1;
 13          exit when l_cursor%notfound;
 14          dbms_output.put_line( l_rec1.username );
 15      end loop;
 16      close l_cursor;

 17
 17

 17      l_cursor := getUser( UID );
 18      loop
 19          fetch l_cursor into l_username, l_user_id, l_created;
 20          exit when l_cursor%notfound;
 21          dbms_output.put_line( l_username );
 22      end loop;
 23      close l_cursor;
 24  
 24      l_cursor := getUser( UID );
 25      loop
 26          fetch l_cursor into l_rec2;
 27          exit when l_cursor%notfound;
 28          dbms_output.put_line( l_rec2.username );
 29      end loop;
 30      close l_cursor;

 31 end;
 32 /

Procedure created.

tkyte_at_8.0> exec showUser
TKYTE
TKYTE
TKYTE PL/SQL procedure successfully completed.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 08:16:51 CST