fetch from function returning a ref cursor to record

oracle, oracle10g, plsql, sql

Solution

I suspect that you think that your cursor should be fetching rows from the REFCURSOR. It's not. The REFCURSOR is itself a cursor, you don't use another cursor to select from it.

What your current cursor is doing is fetching a single row, with a single column, containing the result of the function call. Which is a `record_cursor` not a `record_name`, so you get a type mismatch.

I suspect what you really want to do is something like this:

declare
  symbol_cursor  package_name.record_cursor;
  symbol_record  package_name.record_name;
begin
  symbol_cursor := package_name.function_name('argument');
  loop
    fetch symbol_cursor into symbol_record;
    exit when symbol_cursor%notfound;

    -- Do something with each record here, e.g.:
    dbms_output.put_line( symbol_record.field_a );

  end loop;

  CLOSE symbol_cursor;

end;

Problem

I have a function in a package that returns a REF CURSOR to a RECORD. I am trying to call this function from a code block. The calling code looks like this: ``` declare a_record package_name.record_name; cursor c_symbols is select package_name.function_name('argument') from dual; begin open c_symbols; loop fetch c_symbols into a_record; exit when c_symbols%notfound; end loop; close c_symbols; end; ``` The function declaration as part of package_name looks something like this: ``` TYPE record_name IS RECORD( field_a varchar2(20); ); TYPE record_cursor IS REF CURSOR RETURN record_name; FUNCTION getsymbols(argument IN varchar2) return record_cursor; ``` When I try to run the calling code block, I get the exception: PLS-00386: type mismatch found at 'EXAMPLE_SYMBOLS' between FETCH cursor and INTO variables. What should the type of a_record be and how can I access individual elements of the record I am fetching(of type record_name)?

Original source