3

The question i have is, how can i delete a record on read? I'm using Oracle ond AIX with the Roguewave DB Layer in a c++ application.

I have been searching on google for this answer but there seem only to be the simple examples. Is there a SQL Statement which returns the deleted rows?

This would greatly enhance performance on my application because only 0.1% of the cases will have a need to stay in this table, in other words i will insert 0.1% back into the table.

The only hint i have found is the "Into" clause, i would assume that using delete into would do the job but i have never used it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm

Improve this question

3 Answers 3

Reset to default
5

According to oracle documentation it is indeed possible to delete and read in one go:

DELETE FROM employees
   WHERE job_id = 'SA_REP' 
   AND hire_date + TO_YMINTERVAL('01-00') < SYSDATE 
   RETURNING salary INTO :bnd1;

I never used it myself...but you could give it a try.

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

5 Comments

I think this is what i like to have syntax wise, it doesn't run at the moment but i will try to make it work
Good answer. However, this will only work in a stored procedure, will it? Or can you use it directly from e.g. JDBC?
@sleske: I used the returning clause in select statements using OCCI without stored procedures and it worked just fine. Since this is a different scenario (other db layer) I can't be sure though.
It seems that the bnd1 needs to be turned into a rowtab and every single field requires extraction, there seems to be no trivial statement just to return the deleted rows
Actually you have served me what i asked for, it would have been awesome to have an actual query to return the table but your answer is correct
1

No, there is no SQL construct to read and delete a row in one go.

You could write a stored procedure which does this, though. Or you could cache the records you have already read in memory (so you do not read them again), then later do a bulk delete (DELETE FROM table WHERE id in (?)). That should be faster than many single DELETEs.

Or you might consider a different approach to the problem. Why do you need to delete so many rows on reading them? Are you using a DB table for passing messages? Is there maybe another technology that is more suited to your problem? If you feel you have to work against the established conventions of a technology, that may be an indication that you are not using the right tool for the job...

Improve this answer

Comments

0

Have you tried something like building an audit trigger?

Good description to build an select trigger.

Creation:

begin
  dbms_fga.add_policy
  ( object_schema=>'SCOTT'
  , object_name=>'EMP'
  , policy_name=>'SAL_ACCESS_HANDLED'
  , audit_column => 'SAL'
  , audit_condition => 'SAL>= 1500'
  , handler_schema => 'SCOTT'
  , handler_module => 'AUDIT_HANDLER.HANDLE_EMP_SAL_ACCESS'
  );
end;
/

Function signature:

PROCEDURE HANDLE_EMP_SAL_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
Improve this answer

4 Comments

How would that help OP? Where do the rows get deleted?
I actually haven't considered triggers as a solution, my assumption was that a trigger works like a fork Select --> Data (fork trigger) --> return Data to application, tell me if my understanding is not correct
By changing the insert to an delete inside the called procedure? I have to admit that I have not tried if it is possible to modify the table inside the function without firing again - so maybe a check should be added.
So you would have to parse the statement (get the WHERE clause) and modify it to a DELETE statement and do an execute immediate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.