Sql Insert statement return "zero/no rows inserted"

user_1312739 0
Viewed 22620

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?

insert plsql oracle
3 Answers

To complete one of above comments:

  • in case if you request INSERT INTO ... VALUES DML statement without any hints, then indeed system will return either 1 row(s) created in case of success or ORA error in case of failure

  • however if you request INSERT INTO ... VALUES DML statement with hint: IGNORE_ROW_ON_DUPE_KEY, then you get either 1 row(s) created or 0 row(s) created in case when the row already exists and ORA error in case of other failures

  • and in case you request INSERT ... SELECT as mentioned above by Justin, you can also expect to get 0 row(s) created as internal call to SELECT can indeed return

user_84273720
Related