• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

delete a non existing row  RSS feed

 
Ranch Hand
Posts: 375
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does Oracle 10g throw an exception when you try to delete a non-existing row ?

I tried in my application it does not cause any problem. But I am using java data mapping, so I don't know if the database itself would throw exception when it tries to delete a row but can't find the row ?
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

ben oliver wrote:Does Oracle 10g throw an exception when you try to delete a non-existing row ?

I tried in my application it does not cause any problem. But I am using java data mapping, so I don't know if the database itself would throw exception when it tries to delete a row but can't find the row ?



In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Gerbrand van Dieijen wrote:

In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.



I'm sorry, but that isn't true. It can be caught.

ben oliver wrote:Does Oracle 10g throw an exception when you try to delete a non-existing row ?

I tried in my application it does not cause any problem. But I am using java data mapping, so I don't know if the database itself would throw exception when it tries to delete a row but can't find the row ?



Yes, you could catch this in pl/sql stored procedure by taking advantage of the cursor attribute %NOTFOUND. You would need to do something like the following:



But if it is already deleted... I wouldn't worry about it.

You can also do a select to query for the data and perform exception handling for not returning any data (in PL/SQL you would do the same with an exception block catching the NO_DATA_FOUND exception).
 
Gerbrand van Dieijen
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Campbell wrote:

Gerbrand van Dieijen wrote:

In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.



I'm sorry, but that isn't true. It can be caught.


Just to have the final word, I didn't you can't detect nothing is deleted - I just said with a DELETE FROM won't throw an exception. :-)

Using a stored procedure and plsql code is probably the most efficient solution - Hibernate should be able to handle stored procedures. Don't known about default JPA solutions.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Gerbrand van Dieijen wrote:

Paul Campbell wrote:

Gerbrand van Dieijen wrote:

In any database, you don't actually delete a row, but you execute a DELETE FROM .. WHERE <where_predicate>, the <where_predicate> identifies the row, in case you want to delete a row.

Therefore, Oracle won't throw an exception when you delete a non-existing record/row in a table, it will just don't delete anything.



I'm sorry, but that isn't true. It can be caught.


Just to have the final word, I didn't you can't detect nothing is deleted - I just said with a DELETE FROM won't throw an exception. :-)

Using a stored procedure and plsql code is probably the most efficient solution - Hibernate should be able to handle stored procedures. Don't known about default JPA solutions.



Most of the time... but in the case of a uninitialized nested table or VARRAY... a COLLECTION_IS_NULL error will be thrown.
 
Doody calls. I would really rather that it didn't. Comfort me wise and sterile tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!