Hello,
Can you please help me with an example or a link to dynamically delete data from table.
I would like to supply 3 input parameters (tablename,arg2, arg3) and i want to delete and commit data for every 10K records.
I am thinking to implement it via reference cursor or similar sort off thing.
Below example i coded, i am not dynamically changing the table name, need to figure out and code might not be optimistic.appreciate your inputs.
CREATE OR REPLACE DEL_STG_DATA(TNAME IN VARCHAR2,PROVIDER IN NUMBER,VER_NUM IN NUMBER)
IS
CURSOR C1 AS SELECT PROVIDER,VER_NUM,ROWID AS ROW_ID FROM TNAME;
VAR NUMBER(10);
BEGIN
FOR I IN C1
LOOP
VAR:=VAR+1;
DELETE FROM TNAME WHERE I.ROW_ID=TNAME.ROWID;
IF MOD(VAR%1000)=0 THEN
COMMIT;
END IF;
END LOOP;
END;
Thank you.