Declare record type from object or table type

Question

I have an object type type_object created in the database and want to declare a record type type_record in a package for fetching data in a loop using a ref cursor, which doesn't seem to work with an object type. How can I achieve this without creating a dummy table? I suppose using %ROWTYPE on a type_table as shown below might do the trick but I couldn't figure out how.

/* type_object has been created before */

declare
    type type_table is table of type_object;
    l_rec         type_table%ROWTYPE; -- doesn't work
    l_refcursor   sys_refcursor;
begin
    open l_refcursor for 'select * from mytable';
    loop
      fetch l_refcursor into l_rec;
      exit when l_refcursor%notfound;
      -- do something with l_rec
    end loop;
    close l_refcursor;
end;

Answer 1

You cannot, the syntax does not support it.

---

However, you can use a CURSOR and then %ROWTYPE of the cursor:

declare
  CURSOR l_cursor IS select * from mytable;
  l_rec         l_cursor%ROWTYPE;
begin
  open l_cursor;
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    -- do something with l_rec
  end loop;
  close l_cursor;
end;
/

Or MYTABLE%ROWTYPE:

declare
  l_rec         MYTABLE%ROWTYPE;
  l_refcursor   sys_refcursor;
begin
  open l_refcursor for 'select * from mytable';
  loop
    fetch l_refcursor into l_rec;
    exit when l_refcursor%notfound;
    -- do something with l_rec
  end loop;
  close l_refcursor;
end;
/

or, similar, with a strongly typed cursor:

declare
  TYPE MyTableCurTyp IS REF CURSOR RETURN MyTable%ROWTYPE;
  l_rec         MYTABLE%ROWTYPE;
  l_refcursor   MyTableCurTyp;
begin
  open l_refcursor for select * from mytable;
  loop
    fetch l_refcursor into l_rec;
    exit when l_refcursor%notfound;
    -- do something with l_rec
  end loop;
  close l_refcursor;
end;
/

or use type_object in the query and as the type of the intermediate variable:

declare
  l_type        type_object;
  l_refcursor   sys_refcursor;
begin
  open l_refcursor for 'select type_object(col1, col2, col3, col4)
                        from mytable';
  loop
    fetch l_refcursor into l_type;
    exit when l_refcursor%notfound;
    -- do something with l_type
  end loop;
  close l_refcursor;
end;
/

fiddle