CODEWITHSUNDEEP
oracle-databaseplsqlinsert
Sekhar
Sekhar

Reputation: 343

Sql Insert statement return "zero/no rows inserted"

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?

Upvotes: 9

Views: 25637

Answers (3)

user8427372
user8427372

Reputation: 1

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

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231821

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.

Upvotes: 25

Anjan Biswas
Anjan Biswas

Reputation: 7932

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;

Upvotes: 3

Related Questions