PL/SQ and sqlplus batch by example. Delete from Table in Three Steps

=======================
General
=======================
Generic delete from  a table using Business Logic.
Several million of records are being deleted.
The flow is a three steps process:

Step1.

pre_run_steps.sh

This create backup table for SOURCE_DATA_SUBSCRIBER (SOURCE_DATA_SUBSCRIBER_BAK) 

Creates additional temporary tables which are to be used during actual delete step.


Step2.

main_delete.sh 

The actual DELETE. 

Create the PL/SQL Package, which handles the business logic.

Call the PL/SQL package.

Progress is logged into SGA_W_LOG Table.

Step3.

post_run_steps.sh
This drop the SOURCE_DATA_SUBSCRIBER_BAK and drop additional temporary tables created in pre_run_steps.sh

=======================
Code
=======================

Step 0.
set_vipuser.sql

Step1.

pre_run_steps.sh

pre_run_steps.batpre_run_steps.sql


Step2.

main_delete.shmain_delete.bat

main_delete.sql

MULTI_DELETE.sql

Step3.

post_run_steps.shpost_run_steps.batpost_run_steps.sql

Step 0.
set_vipuser.sql
--Set the connection detailsdefine vipuser=my_userdefine vippass=my_passdefine connectStr=MY_INSTANCE

Step 1.

pre_run_steps.sh
sqlplus /nolog @pre_run_steps.sql

pre_run_steps.bat

sqlplus /nolog @pre_run_steps.sql

pre_run_steps.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2;

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SOURCE_DATA_SUBSCRIBER_BAK'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SOURCE_DATA_SUBSCRIBER_BAK';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

PROMPT INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

COMMIT;

PROMPT CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SGA_LOC_GRP_OUTBACK_NETWORKS'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS (network_id        NUMBER(10) ) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE TABLE   MSISDN_PROFILE_NOT_PRE

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'MSISDN_PROFILE_NOT_PRE'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   MSISDN_PROFILE_NOT_PRE (msisdn VARCHAR2(30) NOT NULL) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE MSISDN_PROFILE_NOT_PRE';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

SELECT DISTINCT network_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUP_ENTRIES 

   WHERE location_group_id IN 

         (SELECT location_group_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUPS 

WHERE LOCATION_GROUP_NAME='Outback');

commit;

PROMPT INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

SELECT KEY1 FROM SOURCE_USER.SFI_CUSTOMER_PROFILE WHERE ATTR1 NOT IN ('RogEngPre','RogFrPre','FidoEngPre','FidoFrPre');

commit;

PROMPT CREATE INDEX SFI_CST_PROF_IX

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_INDEXES WHERE index_name = 'SFI_CST_PROF_IX'; 

  IF v_counter > 0 THEN

    v_sql_str := 'DROP INDEX SFI_CST_PROF_IX';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

BEGIN

  DBMS_STATS.gather_table_stats('&&vipuser','MSISDN_PROFILE_NOT_PRE');

END;

/

PROMPT Finished Pre Run step

EXIT;

Step 2.

main_delete.sh

sqlplus /nolog @main_delete.sql

main_delete.bat

sqlplus /nolog @main_delete.sql

main_delete.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE PROCEDURE MULTI_DELETE

@MULTI_DELETE.sql

PROMPT EXECUTE PROCEDURE MULTI_DELETE

PROMPT .............................

PROMPT Running DELETE....

PROMPT Follow Up on process Progress by: SELECT * FROM SGA_W_LOG ORDER BY TS_LAST_MODIFIED;

PROMPT .............................

BEGIN

  MULTI_DELETE;

END;

/

PROMPT Finished Run step  

EXIT;

MULTI_DELETE.sql

CREATE OR REPLACE PROCEDURE MULTI_DELETE IS

    v_effected_rows NUMBER;

    v_delete_rows NUMBER;

    v_row_counter NUMBER;

    v_table_name VARCHAR2(30);

    v_sql_str VARCHAR2(1000);

    v_module_name VARCHAR2(30);

    v_msg_text VARCHAR2(1000);

  BEGIN

    v_table_name :='SOURCE_DATA_SUBSCRIBER';

    v_effected_rows := 1;

    v_row_counter := 0;

    v_module_name := 'MULTI DELETE';

    v_sql_str := 'DELETE FROM SOURCE_DATA_SUBSCRIBER WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS ) AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE) AND ROWNUM < 100001';

    v_msg_text  := 'Running SQL: '||v_sql_str;

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    v_msg_text  := 'Starting at: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss');

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    WHILE v_effected_rows > 0 LOOP

      DELETE FROM SOURCE_DATA_SUBSCRIBER

       WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS )

         AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE)

         AND ROWNUM < 100001;

      v_effected_rows := SQL%ROWCOUNT;

      v_row_counter := v_row_counter + v_effected_rows;

      COMMIT;

      

      v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;      

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

      

    END LOOP;

    

    COMMIT;

    

    v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

  END MULTI_DELETE;

/

Step 3.

post_run_steps.shsqlplus /nolog @post_run_steps.sql

post_run_steps.bat
sqlplus /nolog @post_run_steps.sql

post_run_steps.sql

@../set_vipuser.sqlSET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK
DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK;

PROMPT DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS
DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS;

PROMPT DROP TABLE MSISDN_PROFILE_NOT_PRE
DROP TABLE MSISDN_PROFILE_NOT_PRE;

PROMPT Finished Post Run step
EXIT;