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.
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.
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...
Have you tried something like building an audit trigger?
Good description to build an select trigger.
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; /
PROCEDURE HANDLE_EMP_SAL_ACCESS ( object_schema VARCHAR2 , object_name VARCHAR2 , policy_name VARCHAR2 );