Saturday, 12 March 2011

PL/SQL Implicit Cursor Attributes

Oracle allows you to access information about the most recently executed SQL statement (INSERT, UPDATE, DELETE and MERGE) by referencing one of the following special implicit cursor attributes:

NameDescription
SQL%FOUNDReturns True if one or more rows were modified (created, changed, removed) successfully.
SQL%NOTFOUNDReturns True if no rows were modified by the DML statement.
SQL%ROWCOUNTReturns the number of rows modified by the DML statement.
SQL%ISOPENAlways returns FALSE for implicit cursors in DML statements.


Remember that you can also use the above on explicit cursors:
DECLARE

    TYPE var_cur_type is REF CURSOR;
    var_cur var_cur_type;
BEGIN
    IF NOT var_cur%ISOPEN THEN
        OPEN var_cur;
    END IF;

    -- Other code goes here
END;