##  An example of a "dynamic SQL" stored database procedure


create or replace PACKAGE Table_Purge
AS 
PROCEDURE Purge 
  ( tab_owner IN varchar2,
    tab_name  IN varchar2,
    col_date  IN varchar2, 
    date_fm   IN date,
    date_to   IN date       default sysdate,
    roll_back IN varchar2   default NULL 
  );
END ;

create or replace PACKAGE body Table_Purge 
AS
  sql_curs       integer;
  sql_text       varchar2 (2000);
  --
  PROCEDURE purge        
  (
    tab_owner IN varchar2,
    tab_name  IN varchar2,
    col_date  IN varchar2, 
    date_fm   IN date,
    date_to   IN date       default sysdate,
    roll_back IN varchar2   default NULL 
  ) 
  IS
    rows_deleted integer := 0;
  BEGIN
    if roll_back is not NULL then
       dbms_transaction.use_rollback_segment ( roll_back );
    end if;
    --
    sql_text := 'delete from ' || tab_owner || '.' ||
                tab_name || ' where ' || col_date ||
                ' between :fm_date and :to_date';
    --
    sql_curs := dbms_sql.open_cursor;
    dbms_sql.parse (sql_curs, sql_text, dbms_sql.v7);
    dbms_sql.bind_variable (sql_curs, 'fm_date', date_fm);
    dbms_sql.bind_variable (sql_curs, 'to_date', date_to);
    rows_deleted := dbms_sql.execute (sql_curs) + rows_deleted;
    dbms_sql.close_cursor (sql_curs);
    --
    commit;
  EXCEPTION
    WHEN others then
        if dbms_sql.is_open (sql_curs) then
           dbms_sql.close_cursor ( sql_curs );
        end if;
        dbms_output.put_line (sql_text);
        raise;
  END purge;
END;


sqlplus>> execute Table_Purge.purge ('scott',
                                     'GL_transactions',
                                     'tran_date',
                                     to_date('01-jan-90'),
                                     to_date('31-dec-92'));

          execute Table_Purge.purge ('scott',
                                     'GL_jrnl_lines',
                                     'jrnl_date',
                                     to_date('01-jan-90'),
                                     to_date('31-dec-92'));