CREATE OR REPLACE PROCEDURE P_NIS_DEL_NONMIGRATED (table_name IN varchar2, column_name IN varchar2) as

cursor s1 is SELECT rowid, t.column_name
from table_name t
where not exists (select LOCN_TTNAME from LOCATIONS where LOCN_TTNAME = column_name); 

-- cursor s1 is SELECT rowid, t.LOCN_TTNAME from LOCATIONS t;
  
i number := 0;
j number := 0;
start_time date := sysdate;
proc_name VARCHAR2(36) := 'P_NIS_DEL_NONMIGRATED_'||table_name;

begin
  for c1 in s1 loop
      delete from table_name where rowid = c1.rowid;

      i := i + 1;              -- Commit after every 1500 records
      if i > 1500 then
         commit;
         i := 0;
      end if;
	
	j := j + 1; 
	
  end loop;
  commit;
  
     dbms_output.put_line(chr(0));
	 dbms_output.put_line('DELETION for '||table_name);
     dbms_output.put_line('-------------------------------------------------');
     dbms_output.put_line(chr(0));
     DBMS_OUTPUT.PUT_LINE (j ||' rows have been deleted!'); 
	 dbms_output.put_line(chr(0));
	 
	insert into PROCEDURE_TRACKING_LOG
	(procedure_name, table_name, rows_inserted, rows_updated, rows_deleted, 
rows_inserted_of_deletion, rows_inserted_after_deletion, 
	rows_rejected, start_time, end_time, 
elapse_time, remarks, subject_area)
	values
	(proc_name,table_name,NULL,NULL,j,NULL,NULL,NULL,start_time,sysdate,
(sysdate-start_time)* 86400,
	NULL,'DELETION (NIS - NON MIGRATED)');
	
  commit;
  
end;
/

CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number := 0;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   j := sql%rowcount;
	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (to_char(j)||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/

exec ZZZ_P_DYNAMIC('ZZZ_DYNAMIC','ANY_COLUMN','SL');


DELETION for ZZZ_DYNAMIC
-------------------------------------------------

0 rows have been deleted!


PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);
   j := sql%rowcount;

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (j||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/

CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);
   j :=DBMS_SQL.LAST_ROW_COUNT ;

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (j||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/

CREATE OR REPLACE PROCEDURE zzz_p_dynamic (
   table_in    IN   VARCHAR2,
   column_in   IN   VARCHAR2,
   string_in   IN   VARCHAR2
)
IS
   sql_query   VARCHAR2 (2000);
   j           NUMBER;
BEGIN
sql_query:='delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''';


   EXECUTE IMMEDIATE sql_query;

   j := SQL%ROWCOUNT;
   COMMIT;
   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line ('DELETION for ZZZ_DYNAMIC');
   DBMS_OUTPUT.put_line ('-------------------------------------------------');
   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line (j || ' rows have been deleted!');
   DBMS_OUTPUT.put_line (CHR (0));
END zzz_p_dynamic;
/

	if j > 500 then
	commit;
	end if;