Sql Insert statement return "zero/no rows inserted"

Question

I am writing an INSERT Statement to insert one row into the table in a PL/SQL block. If this insert fails or no row is inserted then I need to rollback the previously executed update statement.

I want to know under what circumstances the INSERT statement could insert 0 rows. If the insert fails due to some exception, I can handle that in the exception block. Are there cases where the INSERT might run successfully but not throw an exception where I need to check whether SQL%ROWCOUNT < 1?

Answer 1

If your INSERT statement is structured as an INSERT ... VALUES, then it will either successfully insert exactly one row or generate an exception. There would be no need to check the SQL%ROWCOUNT.

If your INSERT statement is structured as an INSERT ... SELECT, then it is possible that the SELECT statement will return 0 rows, the INSERT statement will insert 0 rows, and no exception will be thrown. If you consider that to be an error, you would need to check the SQL%ROWCOUNT after the INSERT statement runs.

Answer 2

Yes, to find out how many rows are affected by DML statements (INSERT, UPDATES etc.), you can check the value of SQL%ROWCOUNT

INSERT INTO TABLE
SELECT col1, col2,....
  FROM TAB;

if SQL%ROWCOUNT=0 then
   RAISE_APPLICATION_ERROR(-20101, 'No records inserted');
end if;