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?

Is your INSERT statement an INSERT ... VALUES statement? Or an INSERT ... SELECT? — Justin Cave
– Justin Cave, Commented Aug 9, 2012 at 5:11

Justin Cave · Accepted Answer · 2012-08-09 05:22:53Z

26

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.

answered Aug 9, 2012 at 5:22

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gili Over a year ago

Excellent answer! I've always wondered how this could happen.

Anjan Biswas · Accepted Answer · 2012-08-09 05:21:18Z

3

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;

answered Aug 9, 2012 at 5:21

Anjan Biswas

8,0126 gold badges51 silver badges78 bronze badges

Comments

user8427372 · Accepted Answer · 2019-06-26 08:54:38Z

0

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

answered Jun 26, 2019 at 8:54

user8427372

1

Collectives™ on Stack Overflow

Sql Insert statement return "zero/no rows inserted"

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related