Shell Programming and Scripting

DELETE FROM table_name
WHERE some_column=some_value   # here put your condition in where clause currentdatetimestamp > columnDate

SQL>
SQL>
SQL> --
SQL> -- Check the data in the dummy table
SQL> --
SQL> select * from t;
        X Y Z
---------- - ---------
        1 A 01-JAN-12
        2 B 01-FEB-12
        3 C 01-MAR-12
        4 D 01-APR-12
 
4 rows selected.
 
SQL>
SQL> --
SQL> -- My DELETE statement is as follows:
SQL> --
SQL> -- #####################################################
SQL> -- delete
SQL> --   from t
SQL> --  where z >= to_date ('02/01/2012', 'mm/dd/yyyy');
SQL> -- #####################################################
SQL> --
SQL> -- So I know that IDs 2, 3 and 4 will be deleted, and
SQL> -- hence those are the ones that should be returned.
SQL> --
SQL>
SQL> --
SQL> -- Now create a nested table to hold those IDs
SQL> --
SQL> create type tbl_num as table of number;
 2  /
 
Type created.
 
SQL>
SQL> --
SQL> -- Use the "RETURNING" clause of Oracle's DELETE
SQL> -- statement to return the IDs that were deleted.
SQL> -- Then loop and print
SQL> --
SQL> declare
 2    tbl_x  tbl_num;
 3  begin
 4    --
 5    delete
 6      from t
 7     where z >= to_date ('02/01/2012', 'mm/dd/yyyy')
 8    returning x
 9    bulk collect into tbl_x;
10    --
11    for i in tbl_x.first .. tbl_x.last
12    loop
13      dbms_output.put_line ('Deleted Id = ' || tbl_x(i));
14    end loop;
15  end;
16  /
Deleted Id = 2
Deleted Id = 3
Deleted Id = 4
 
PL/SQL procedure successfully completed.
 
SQL>
SQL>
SQL>