CODEWITHSUNDEEP
sqloracle-database
Jay
Jay

Reputation: 97

Capture rows for a column in file from delete sql -Oracle

I have to capture rows for a column that was deleted. How can i do that without having to write a select query?

delete from myschema.mytable where currentdatetimestamp > columnDate

this should delete 5 rows for example

I want to capture rows of columnID (not all the columns of the table) in a file that was affected by the above file should have 

1234
1235
1236
1237

1238

Upvotes: 0

Views: 453

Answers (2)

DCookie
DCookie

Reputation: 43523

You can use the RETURNING clause of the DELETE statement (Adapted this example in the Oracle Docs):

DECLARE
   TYPE NumList IS TABLE OF myschema.mytable.columnID%TYPE;
   IDs NumList;
BEGIN
   DELETE FROM myschema.mytable WHERE currentdatetimestamp > columnDate
      RETURNING columnID BULK COLLECT INTO IDs;
   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN IDs.FIRST .. IDs.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('columnID: '||IDs(i));
   END LOOP;
END;
/

You can do whatever you like with the resulting collection of IDs.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

It may be possible to do what you want by using a DELETE trigger. Following is an example:

CREATE OR REPLACE TRIGGER MY_TABLE_DELETE_TRIGGER
  BEFORE DELETE ON MY_TABLE
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Deleted row with ID=' || :OLD.COLUMN_ID);
END MY_TABLE_DELETE_TRIGGER;

This is just an example - you should modify it to do whatever you need.

Share and enjoy.

Upvotes: 0

Related Questions