Re: newbie plsql question

In article <8v2rf8$2an$1_at_nnrp1.deja.com>,   sybrandb_at_my-deja.com wrote:
> In article <8v2nkp$un7$1_at_nnrp1.deja.com>,
> george096_at_my-deja.com wrote:
> > Hopefully it is an easy question but I seem to find no mention to it
 in
> > the pl sql manual.
> >
> > How to declare a cursor variable type that return rows from multiple
> > tables?
> >
> > The examples in manual show:
> >
> > TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
> >
> > where %rowtype represents the rows in the emp_tab table.
> >
> > What if my cursor will select from multiple tables with join?
> >
> > How to declare the rowtype?
> >
> > In procedure, I can do something like:
> >
> > cursor c1 is select... from table1, table2, table3 where ...;
> > r1 c1%rowtype;
> >
> > but I want to use packages and need to declare the cursor type in
 the
> > package declaration. can %rowtype support more than 1 table here?
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
> You'll need to use a weakly typed ref cursor. so REF CURSOR without
 any
> %ROWTYPE spec.
>
> Hth,
> --
> Sybrand Bakker, Oracle DBA

Thank you for the help. I have a further problem.

I have 2 question. I'm not happy with oracle pl/sql documentation. I just don't get it. All code examples don't show how to handle weak type cursors in code. Is there a better book?

I'm just practicing. I'm trying make this package work. I got this example from oracle pl/sql manual. I'm trying to modify it to work with weak type cursor that retrieves from 2 tables. it complains on creating the package body. It doesn't like the line r1 c1%rowtype. The first procedure opens the cursor. The 2nd procedure loops on the rows in the cursor and displays the output. If I do this in standalone  procedure, it works. But I don't know how to declare the rowtype of a weak type cursor in context of package and procedure. Please help.

CREATE or replace PACKAGE test2 AS

   type csrtype is ref cursor;
   procedure testproc(cs OUT csrtype);
   procedure testproc2(cs IN csrtype);
END test2;
/
CREATE or replace PACKAGE BODY test2 AS
PROCEDURE testproc(cs OUT csrtype) IS

   BEGIN
open cs for select id, name from account a, sdmmdn b where a.account_id=b.domain_id;
END testproc;

PROCEDURE testproc2(cs IN csrtype) IS

   r1 cs%rowtype;
   BEGIN
dbms_output.enable(1000);
loop
  fetch cs into r1;
  exit when cs%notfound;
  dbms_output.put_line(r1.id||' --- 'r1.name); end loop;
END testproc2;
END test2;
/

thank you.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Nov 18 2000 - 17:22:28 CST