rowtype variables within nested procedures

From: Ken Denny <kdenny_at_interpath.com>
Date: 1997/05/23
Message-ID: <338646C5.3DDD@interpath.com>#1/1

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. The code in any_diffs is rather lengthy and I call it from three different places in check_changes, so I really don't want to have to repeat it inline three times. It would appear my only option would be to use a rowtype variable for a parameter. I was wondering if this is possible, i.e.

function any_diffs(part_row parts%rowtype) return boolean is . . .

then in check_changes:

if any_diffs(r_part) then
. . .

I'm also wondering why I am experiencing the behavior I'm seeing. Sorry, I don't have the level of PL/SQL I'm using handy, but the Oracle level is 7.2.3. Can anyone offer any suggestions. Please reply via email.

Thanks
Ken Denny
e-mail: kdenny_at_interpath.com Received on Fri May 23 1997 - 00:00:00 CDT