Hello,

I have performance problems with Oracle 9i.

A stored procedure returns a cursor as an OUT parameter :

Code:
CREATE my_proc (
  curs OUT TYPES.type_my_curs
)
AS
BEGIN
  OPEN curs FOR
    SELECT DISTINCT...
    FROM ...
    WHERE ...
    ORDER BY ... ;
END;
The query is optimized, the select alone takes less than a second to execute.

Here is an extract from the package :

Code:
CURSOR my_curs IS
  SELECT ...
  FROM ...;
TYPE type_my_curs IS REF CURSOR RETURN my_curs%ROWTYPE;
My problem is, when I try to FETCH results from my cursor, the time to execute climbs to more than a minute...

Code:
declare
  my_c TYPES.type_my_curs;
  my_var ...
begin
  my_proc(my_c);
  loop
    exit when my_c%NOTFOUND;
    fetch my_c into my_var;
  end loop;
Anyone has an idea ? Thanks in advance !