sql_mode=ORACLE: Explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND

Understand explicit cursor attributes when running in sql_mode=ORACLE:

• cursor_name%ISOPEN
  Return TRUE if cursor_name was already open with OPEN, or FALSE otherwise

• cursor_name%ROWCOUNT
  Return 0 before the first FETCH, afterwards return the number of rows that were already fetched from cursor_name using FETCH.

• cursor_name%FOUND
  Return NULL before the first FETCH. Return TRUE if the last FETCH returned a row, or FALSE otherwise.

• cursor_name%NOTFOUND
  Return NULL before the first FETCH. Return TRUE if the last FETCH returned no rows, or FALSE if the last FETCH returned some rows.

Due to a grammar conflict, this task will remove the modulo operator '%'. One should use MOD in Oracle.

This task will also implement the predefined exception INVALID_CURSOR. If a cursor is not open, referencing it with %FOUND, %NOTFOUND, or %ROWCOUNT raises INVALID_CURSOR.

If a cursor is closed and then opened again, %FOUND and %NOTFOUND are reset to NULL and %ROWCOUNT is reset to 0.

Note, we'll also change behavior of the FETCH statement to raise no conditions (exceptions) if no rows were returned. Instead of catching conditions, in Oracle one should test %FOUND or %NOTFOUND attributes to know if FETCH returned a row.

Example:

  BEGIN

    FETCH cur INTO v1,v2;

    EXIT WHEN cur%NOTFOUND;

    ...

  END;