CODEWITHSUNDEEP
sqloracle-databaseplsql
aw crud
aw crud

Reputation: 8891

Can a SQL INSERT fail without raising an exception in PL/SQL

Are there any cases where an INSERT in SQL (specifically Oracle-PL/SQL) can fail without an exception being thrown? I'm seeing checks in code after INSERT where it verifies that SQL%ROWCOUNT = 1 ELSE it raises its own user-defined exception. I don't see how that can ever happen.

Upvotes: 3

Views: 3704

Answers (3)

BQ.
BQ.

Reputation: 9413

As @TonyAndrews and @GinoA mentioned, there are several ways an INSERT could return something other than exactly one row (triggers, INSERT INTO tablename SELECT... syntax).

But the bigger issue is that you're in PL/SQL. As such, the SQL%ROWCOUNT value can be used as a condition to determine the program execution flow including issuing COMMIT or ROLLBACK statements.

Even with just raising a user-defined exception, the calling PL/SQL block can handle the exception itself.

EDIT: Someone should modify the question title to indicate PL/SQL (as indicated in the question itself), since that's not the same thing as SQL scope the title suggests.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

It can't fail without an exception, no. Probably the developer who wrote the code didn't know that.

An after statement trigger could conceivably delete the row just inserted. And of course an INSERT...SELECT might find no rows to insert, and so would result in SQL%ROWCOUNT = 0.

Upvotes: 8

GinoA
GinoA

Reputation: 732

In addition to the trigger-based issue @mcabral mentioned, you could have an insert that is successful but inserts other than 1 row. For example, the insert into blah(col1) select col2 from foo style of insert.

Upvotes: 2

Related Questions