Oracle Cursor Attributes
Version 18.3.0.1

SQL%FOUND
Were any rows affected Returns NULL if not statement has been run, TRUE if a SELECT statement returned one or more row: Otherwise FALSE.
conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 x servers.srvr_id%TYPE;
BEGIN
  SELECT srvr_id
  INTO x
  FROM servers
  WHERE srvr_id > 100
  AND rownum = 1;

  IF SQL%FOUND THEN
    dbms_output.put_line('Found Server ID: ' || TO_CHAR(x));
  END IF;
END;
/
 
SQL%ISOPEN
Is the cursor open Always returns FALSE with internal cursors but useful with explicitly declared cursors
conn uwclass/uwclass@pdbdev

DECLARE
 CURSOR iCur IS
 SELECT srvr_id
 FROM servers;
BEGIN
  IF iCur%ISOPEN THEN
    dbms_output.put_line('1: ' || 'iCur Is Open');
  ELSE
    dbms_output.put_line('1: ' || 'iCur Is Not Open');
  END IF;

  OPEN iCur;
  IF iCur%ISOPEN THEN
    dbms_output.put_line('2: ' || 'iCur Is Open');
  ELSE
    dbms_output.put_line('2: ' || 'iCur Is Not Open');
  END IF;
END;
/
 
SQL%NOTFOUND
Were no rows affected
conn uwclass/uwclass@pdbdev

set serveroutput on

BEGIN
  DELETE FROM servers
  WHERE srvr_id < 0;

  IF SQL%NOTFOUND THEN
    dbms_output.put_line('No Rows Found That Match The Filter Condition');
  END IF;
END;
/
 
SQL%ROWCOUNT
Counter for the number of rows affected by a statement conn uwclass/uwclass@pdbdev

CREATE TABLE t (
testcol VARCHAR2(10));

INSERT INTO t (testcol) VALUES ('ABCDEFG');
INSERT INTO t (testcol) VALUES ('ABCXEFG');
INSERT INTO t (testcol) VALUES ('ABCYEFG');
INSERT INTO t (testcol) VALUES ('ABCDEFG');
INSERT INTO t (testcol) VALUES ('ABCZEFG');
COMMIT;

SELECT * FROM t;

BEGIN
  UPDATE t
  SET testcol = 'UPDATED'
  WHERE testcol LIKE '%D%';

  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO t
    (testcol)
    VALUES
    ('12345567');
  END IF;
END;
/

SELECT * FROM t;

BEGIN
  UPDATE t
  SET testcol = 'UPDATED'
  WHERE testcol LIKE '%S%';

  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO t
    (testcol)
    VALUES
    ('0000000');
  END IF;
END;
/

SELECT * FROM t;

Related Topics
DBMS_SYSTEM.GET_ENV
Exception Handling
System Events
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved