AntDB-Oracle Compatibility Developer’s Manual P4–22

Get the Result Status

There are some properties that can be used to confirm the execution of the command. SQL%FOUND is a boolean property that returns "true" when an INSERT, UPDATE, or DELETE command does a valid operation on a row, or when a SELECT INTO command takes out more than one row.

The following anonymous block inserts a row, and then displays the result of the insert operation.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert() 
IS
BEGIN
    INSERT INTO emp (empno,ename,job,sal,deptno) VALUES (9001, 'JONES', 'CLERK', 850.00, 40);
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Row has been inserted');
    END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_insert();
NOTICE:  Row has been inserted
 EMP_INSERT
------------

(1 row)

SQL%ROWCOUNT provides the number of valid operations performed on a row by the INSERT, UPDATE or DELETE commands. The following example updates the row inserted in the previous example and displays the value of SQL%ROWCOUNT.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
    UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001;
    DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT);
END;

/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  # rows updated: 1
 EMP_UPDATE
------------

(1 row)

SQL%NOTFOUND is the opposite of SQL%FOUND, and returns 'true' if the INSERT, UPDATE or DELETE command was unsuccessful on the record, or if the SELECT INTO command did not retrieve any data.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
    UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9000;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows were updated');
    END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  No rows were updated
 EMP_UPDATE
------------

(1 row)