Re: PL SQL variable/scope question

I *think* I have done something similar to what you are asking. I defined one cursor as a "dummy" and two "real" cursors. Based on some condition I would call my procedure using one or the other of the "real" cursors as input. The "dummy" cursor was used in the procedure definition and as I looped through the records of whichever cursor I would pass that row to the procedure.

Something like

 cursor find_DUMMY is
   select * from table;

 cursor find_rows1 is

    select * from table where....
 cursor find_rows2 is

    select * from table where (completely different variables tested)  cursor find_rows3 is

    select * from table where (completely different tests)

procedure format_proc( in_Cursor find_dummy%rowtype ) is
begin
  ...
end;

 if a then
   format_proc( find_rows1 );
 elsif b then

     format_proc( find_rows2 )
 else
 ... etc

"Jay Scheiner" <jayms_at_nospamaol.com> wrote in message news:3ba41123.21263433_at_news.starpower.net...
> Let me expand on my problem:
>
> cursor find_rows1 is
> select * from table where....
> cursor find_rows2 is
> select * from table where (completely different variables tested)
> cursor find_rows3 is
> select * from table where (completely different tests)
>
> if a then
> cursor find_rows1 loop
> fomat_proc;
> end loop;
> elsif b then
> cursor find_rows2 loop
> format_proc
> end loop;
> else
> ... etc
>
> All 3 loops are doing something with table%rowtype, but 1 common
> format procedure. So if I made the ct correspond to one cursor
> rowtype, I can't use it with the other cursors.
>
> I'm not sure how clearly my psuedo-code is coming across. If it's not
> clear, I will post the full code.
>
> On Sat, 15 Sep 2001 09:18:28 -0700, "larry.j" <ljohnson_at_lodinet.com>
> wrote:
>
> >Try changing cr definition to "ct find_rows%rowtype;"
> >
> >Larry Johnson
> >KeepItSimpleEngineering,Inc.
> >
> >"Jay Scheiner" <jayms_at_nospamaol.com> wrote in message
> >news:3ba27a3e.10208625_at_news.starpower.net...
> >> I am having trouble figuring out why this isn't working (this is a
> >> shortened version to clarify my problem)
> >>
> >> create procedure misc as
> >> cursor find_rows .....;
> >> cr table%rowtype;
> >> x number(10);
> >> procedure subr as
> >> begin
> >> x := cr.item
> >> end subr;
> >> --
> >> for cr in find_rows loop
> >> subr;
> >> end loop;
> >>
> >> The problem is that inside the subroutine the cursor row (cr) seems to
> >> be empty. Do I need to refer to it differently? Pass it as a
> >> parameter? Declare it as global (how)? Thanks for any help on this.
> >
> >
>
Received on Mon Sep 17 2001 - 08:36:52 CDT