• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

delete a non existing row

 
Ranch Hand
Posts: 375
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic