Skip to content

Instantly share code, notes, and snippets.

@johnhonan

johnhonan/del_rows_w_backup.sql

Created December 19, 2017 19:57
Show Gist options
  • Save johnhonan/a562232bd044c0c880b314a7951d58eb to your computer and use it in GitHub Desktop.
Save johnhonan/a562232bd044c0c880b314a7951d58eb to your computer and use it in GitHub Desktop.
Download ZIP
EXECUTE IMMEDIATE to first create a timestamped backup of a table and then delete rows
Raw
del_rows_w_backup.sql
-- This shows how to create a procedure
-- which uses EXECUTE IMMEDIATE to first create a timestamped backup of a table
-- before deleting all the rows from that table
CREATE TABLE hr.my_test_table2 AS SELECT * FROM hr.EMPLOYEES;
CREATE OR REPLACE PROCEDURE hr.del_rows
(p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE hr.'||p_table_name||'_'||to_char(sysdate,'yyyy_mm_dd')||' AS SELECT * FROM hr.'||p_table_name;
EXECUTE IMMEDIATE 'delete from '||p_table_name;
p_rows_deld := SQL%ROWCOUNT;
END;
/
VARIABLE deleted NUMBER;
EXECUTE hr.del_rows('my_test_table2',:deleted);
PRINT deleted;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment