Cursors as Parameters and Variables
A cursor is tied to a specific query.
A cursor variable is not tied to a specific query. It is also known as a REF CURSOR.
A REF CURSOR is considered strong if it includes a return type. A strong cursor variable type
declaration is usually followed by the declaration of the cursor itself.
TYPE employeeCursorType IS REF CURSOR RETURN employees%ROWTYPE;
It is considered weak if it does not include a return type.
TYPE weakCursorType IS REF CURSOR;
Strong REF CURSOR types are safer (less error prone) because you are limited to
associating them with queries that return a specified set of columns. This is somewhat analogous
to coding in a strongly typed language where variable types are clearly defined and casting
is very controlled. Weak REF CURSOR types are more flexible because this limitation is not
enforced. Any query can be referenced - there is no type checking. This flexibility can
lead to errors if an unexpected query is referenced by the cursor and the results contain
It is common to see legacy Oracle PL/SQL packages with REF CURSOR type declarations.
Because a weak REF CURSOR has no declared structure, recent versions of Oracle include the predefined
type SYS_REFCURSOR that can be used wherever a weak ref cursor is needed. You can use this
in recent Oracle versions instead of declaring your own REF CURSOR type.