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

Question

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

1238

What kind of "file"? Another database? A table in the same database? A text file? — Ken White
– Ken White, Commented Sep 8, 2012 at 1:15
"...capture rows for a column that was deleted", I'm afraid you can't. Rows are already deleted right? — John Woo
– John Woo, Commented Sep 8, 2012 at 1:25
@KenWhite - yes write the data to a text file before it gets deleted. — Jay
– Jay, Commented Sep 8, 2012 at 1:29
The only way to do this would be in a DELETE trigger, as @BobJarvis says below - it is called as the rows are being deleted though, not after. Once they're deleted, it's too late. What are you actually trying to accomplish, though? There's probably a better way to do it than with a text file. — Ken White
– Ken White, Commented Sep 8, 2012 at 1:34

DCookie · Accepted Answer · 2012-09-08 04:18:55Z

1

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.

edited Sep 8, 2012 at 4:18

answered Sep 8, 2012 at 4:09

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

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

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2012-09-08 01:31:51Z

0

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.

answered Sep 8, 2012 at 1:31

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

50.2k10 gold badges81 silver badges119 bronze badges

4 Comments

Ken White Over a year ago

I agree that would be the only possible way to do it. I'm not sure of what the purpose would be, though; since the poster didn't explain what the reason for doing so is, it's hard to say whether this is the right answer or not though.

Jay Over a year ago

Thanks - i want to capture those IDs because there are corresponding files on the file system that needs to be deleted as well. So i will loop through file and delete 1234.xml, 1235.xml, etc..

Bob Jarvis - Слава Україні Over a year ago

@Jay - perhaps you could use UTL_FILE.FREMOVE()? The problem here is that if your transaction get rolled back for some reason, the file would be removed but the row would still remain.

Jay Over a year ago

@BobJarvis - i am not familiar with that. Let me read on it. Thanks!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related