Re: rowtype variables within nested procedures

Ken Denny wrote:
>
> Hi,
>
> I am having a problem with a stored procedure I'm trying to write.
> Basically the structure is like this:
>
> /* Begin PL/SQL code */
> procedure check_changes is
> cursor c_part(sn varchar2, site number) is
> select * from parts where serial_no=sn and site_no=site
> for update;
> r_part parts%rowtype;
> function any_diffs return boolean is
> begin
> . . .
> end;
> begin
> for r_part in c_part(serialx,sitex) loop
> . . .
> if any_diffs then
> . . .
> end if;
> . . .
> end loop;
> end check_changes;
> /* End PL/SQL code */
>
> It appears that whenever I reference r_part.anything within the
> any_diffs function it is null.
> (snip

The problem is in the construction "for r_part in c_part".

It appears that this creates a structure which does not correspond exactly to parts%rowtype (no, I don't know why).

You can check this by using "for r1_part in c_part(..." where r1_part is implicit (undefined). r1_part.anything is perfectly well-behaved within the loop, but if you also define r_part as c_part%rowtype then the assignment "r1_part := r_part;" gives an error!

However, the values of r_part *are* accessible to your function if you use the explicit structure:
open C ...
loop

   fetch C into r_part; -- r_part *must* be explicitly defined    ...
   if any_diffs then
     ...
   end if;
   ...
end loop;
close C;

Hope this helps.

Chrysalis. Received on Mon May 26 1997 - 00:00:00 CDT