Re: PL/SQL problem

>declare
>i number;
>k number;
>type var_record is record
>(user manager_close.user_id%type,
> var number);
>type var_record_table is table of var_record index by binary_integer;
>var_table var_record_table;
>begin
> i:=dbms_utility.get_time;
> select user_id, nvl(count(*),0) into var_table from manager_close
>where when_closed = '13-JUL-99' group by user_id;
> k:=dbms_utility.get_time;
> dbms_output.put_line(k-i);
>end;

Oracle is picky - when checking to see if things match, it checks the definition and does not check the things inside the definition.

To get it to work, you need to do something like this:

declare
cursor my_data is select user_id, count(*) from manager_close

      where when_closed='13-Jul-99' group by user_id; my_rec my_data%rowtype;
begin

open my_data;
fetch my_data into my_rec;
close my_rec;

end;

However, it really can be easier than that when looping - you don't even need to declare the record if you are not using it outside the loop...

declare
cursor my_data is select user_id, count(*) user_count from manager_close

      where when_closed='13-Jul-99' group by user_id;

begin

for my_rec in my_data loop
  <my_rec has components my_rec.user_id and my_rec.user_count.Use then however you want>
end loop;

end;

This construct will open and execute the cursor, ending the loop automatically when all rows are processed, and close the cursor.

Dan Hekimian-Williams Received on Tue Aug 03 1999 - 07:27:03 CEST