7 Replies Latest reply: Jan 24, 2014 2:46 PM by jgarry RSS

Batch script to delete records

LouieWarren Newbie
Currently Being Moderated

I have an Oracle 10 DB that I am "in charge of", however, there are other gov't DBAs that actually control it.    I am working with a tool that was developed by the previous contract winner and they are no help.  The tool I use allows for records to be created, and for the most part, edited.  However, there is no way to back out something if you make a mistake.   I was told by the gov't DBA to create a batch script with exactly the records I want deleted and he'd run it.    There are 16 tables and the criteria is the same for each; a specific date, and userID is the same for each record.... 

 

DELETE FROM Table1 WHERE DATE = '01-24-2014'  and USERID = 'Me';   I need to do that 16 times and only change the table name.    I haven't written a script in 15 years and old age is messing with me.   Anyone have a suggestion?  Thank you in advance.

  • 1. Re: Batch script to delete records
    rp0428 Guru
    Currently Being Moderated

    Seriously? You can't think of a simple way to do that? Just put these statements into a file.

    DELETE FROM Table1 WHERE DATE = '01-24-2014'  and USERID = 'Me';

    DELETE FROM Table2 WHERE DATE = '01-24-2014'  and USERID = 'Me';

    . . .

    DELETE FROM Table16 WHERE DATE = '01-24-2014'  and USERID = 'Me';

     

    Add a commit at the end if you want. Don't make simple things complicated.

  • 2. Re: Batch script to delete records
    LouieWarren Newbie
    Currently Being Moderated

    Yeah, seriously.  Sorry for the intrusion. As I said, I'm old and haven't done it in quite a while.

  • 3. Re: Batch script to delete records
    rp0428 Guru
    Currently Being Moderated

    It's not an 'intrusion'.

     

    I've just never understood why anyone would want to waste time trying to do something the complicated way when the simple way is much easier and takes less time.

     

    You posted at 12:20 pm; I responded at 12:29 pm.

     

    In those 9 minutes you could have just copied and pasted that simple DELETE statement 15 times and changed the table name.

  • 4. Re: Batch script to delete records
    jgarry Guru
    Currently Being Moderated

    Yeah, but the QA and testing will take weeks!

     

    But seriously, DBA's telling people to make a script they will "just run" in production, could be good or very, very bad.  The commit is worrisome; I'd want someone to watch and be sure each statement comes back with the expected number of rows before committing.

     

    I see it all the time; people sending data that is supposed to be clean and isn't.  So I make scripts that add documentation for each thing that happens, and run it twice, first with a rollback.  Yes, this is very inefficient.  Yes, it has saved a number of behinds.  Yes, it is still not enough.  Yes, one statement can ruin everyone's day.

  • 5. Re: Batch script to delete records
    rp0428 Guru
    Currently Being Moderated
    The commit is worrisome; I'd want someone to watch and be sure each statement comes back with the expected number of rows before committing.

    Ahh - yet another reminder of the old 'desktop computing' era.

     

    Using Paradox:

    1. a DELETE query would not only delete the rows but would create a table named 'DELETED' that actually contained the rows that were deleted. Then you could examine them and even put them back with an INSERT if you wanted.

     

    2. an UPDATE query would perform the update and create a table named 'UPDATED' containing the original rows.

     

    3. an INSERT query would insert and also create a table name 'INSERTED' containing a copy of the rows inserted.

     

    I was always looking for Oracle to implement a CTAD - 'create table as delete' to make it easier to implement auditing and controls.

  • 6. Re: Batch script to delete records
    jgarry Guru
    Currently Being Moderated

    Well, if you are "cat juggling" your production data, flashback may not be enough!

  • 7. Re: Batch script to delete records
    Mark D Powell Guru
    Currently Being Moderated

    The smart or scary-cat method would be to first write the 15 statements as select statements, run the selects to ensure each returns one an only one row, convert the select statement into delete statements.  You can then pass the script to the DBA to run for you.  The script should echo the commands, have feedback on, spool the results, and issue a message to the DBA to issue a COMMIT if each delete responded with one row deleted.

     

    set echo on

    set feedback on

    spool manual_deletes

    delete t1 where ..

    delete t2 where ...

    select 'If all statements returned one row deleted then Commit else Rollback' from sys.dual;

    spool off

     

    As RP said, keep it simple.

     

    HTH -- Mark D Powell --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points