Hi All,
we have a database where temporary tables(around 200-300 tables) will be updated by the load process, I am trying to write a procedure to delete the data from all the TMP tables, if the data is older than the value which is defined in the parameter table.
I am not able to find why the procedure is failing when I am trying to compile it. Can any one have a look and please let me know.
This is my first procedure, request your kind inputs for this.
CREATE OR REPLACE PROCEDURE DELETE_DATA IS
v_table_name varchar2(255);
v_time_value INTEGER;
CURSOR get_tables IS
SELECT DISTINCT tbl.table_name
FROM all_tables tbl
WHERE tbl.table_name LIKE '%TMP';
CURSOR get_time_value IS
SELECT time_value from param;
BEGIN
OPEN get_tables;
LOOP
FETCH get_tables INTO v_table_name;
OPEN get_time_value;
LOOP
FETCH get_time_value INTO v_data_retention_for;
sqlstatement = 'DELETE FROM ' || v_tablename || ' WHERE MONTHS_BETWEEN(SYSDATE, ' ||
UPDATE_DATE || ' ) > ' || v_time_value || ;
LOOP
EXECUTE IMMEDIATE sqlstatement;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
CLOSE get_time_value;
END LOOP;
CLOSE get_tables;
end DELETE_DATA;
Regards,
Deepti