Get specific fields from sys_refcursor in stored proc?

I am running an Oracle 9i server at my office. I am working on a procedure that passes a sys_refcursor as an out parameter to another package (along with other in parameters). I was able to define a type as a record of the various columns that the called procedure returns in the cursor. I can then loop over with code like this:

LOOP
   fetch o_results into v_rec;
   exit when o_results%notfound;
   dbms_output.put_line(v_rec.some_id);
end loop;

Is there a way to only pull one column and not have to declare an entire rowtype? I tried something like:

LOOP
  fetch o_results.some_id into v_id;
  exit when o_results%notfound;
  dbms_output.put_line(v_id);
end loop;

But that didn't work. Any othe开发者_StackOverflow中文版r ideas?

No, you cannot fetch a single column into a local variable other than a record if the cursor returns a result set with multiple columns. However, you do have a few alternatives.

If you declare a strongly-typed cursor rather than a weakly typed cursor, you could declare your local variable based on that cursor definition rather than declaring a new collection.

create or replace procedure cursor_proc
as
  cursor emp_cur
      is
   select empno, ename
     from emp;
  l_row emp_cur%rowtype;
begin
  open emp_cur;
  loop
    fetch emp_cur into l_row;
    exit when emp_cur%notfound;
    dbms_output.put_line( l_row.ename );
  end loop;
  close emp_cur;
end;

Alternately, if you know that the weakly typed ref cursor will always return all the columns in a particular object, you can anchor your local variable declaration to that object. You can always make this work by declaring a view that your cursor selects from. For example

create or replace view vw_emp
as
select ename, empno
  from emp

create or replace procedure cursor_proc2
as
  emp_cur sys_refcursor;
  l_row   vw_emp%rowtype;
begin
  open emp_cur for select * from vw_emp;
  loop
    fetch emp_cur into l_row;
    exit when emp_cur%notfound;
    dbms_output.put_line( l_row.ename );
  end loop;
  close emp_cur;
end;

Finally, if you use an implicit cursor, Oracle will implicitly declare the collection type

create or replace procedure cursor_proc3
as
begin
  for emp in (select ename, empno from emp)
  loop
    dbms_output.put_line( emp.ename );
  end loop;
end;