By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table parameters (name varchar2(30), param varchar2(30));
insert into parameters (name, param) values ('rententionPeriod', 42);
1 rows affected
create table tbl1 (update_date date);
create table tbl2 (update_date date);
create table tbl1_arc (update_date date);
create table tbl2_arc (update_date date);
CREATE OR REPLACE PROCEDURE PURGE_PROGRAM
AS
BEGIN
DECLARE
v_param VARCHAR2(3500);
v_sql VARCHAR2(500);
v_purge_count NUMBER(17);
BEGIN
SELECT param INTO v_param FROM PARAMETERS WHERE NAME='rententionPeriod';
dbms_output.put_line('Param: '||v_param);
IF v_param IS NOT NULL THEN
SELECT COUNT(*) INTO v_purge_count
FROM
(
SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
UNION ALL
SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
)x;
v_sql := 'INSERT INTO tbl1_arc
SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param';
EXECUTE IMMEDIATE v_sql;
v_sql := 'INSERT INTO tbl2_arc
SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param';
EXECUTE IMMEDIATE v_sql;
END IF;
END;
END PURGE_PROGRAM;
/
begin
dbms_output.enable;
purge_program;
end;
/
dbms_output:
Param: 42
ORA-00904: "V_PARAM": invalid identifier
ORA-06512: at "FIDDLE_XTPUGNXMDJWFLFLGZJSG.PURGE_PROGRAM", line 24
ORA-06512: at line 3
CREATE OR REPLACE PROCEDURE PURGE_PROGRAM
AS
BEGIN
DECLARE
v_param VARCHAR2(3500);
v_sql VARCHAR2(500);
v_purge_count NUMBER(17);
BEGIN
SELECT param INTO v_param FROM PARAMETERS WHERE NAME='rententionPeriod';
dbms_output.put_line('Param: '||v_param);
IF v_param IS NOT NULL THEN
SELECT COUNT(*) INTO v_purge_count
FROM
(
SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
UNION ALL
SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > v_param
)x;
v_sql := 'INSERT INTO tbl1_arc
SELECT * FROM tbl1 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > :v_param';
EXECUTE IMMEDIATE v_sql USING v_param;
v_sql := 'INSERT INTO tbl2_arc
SELECT * FROM tbl2 WHERE TRUNC(SYSDATE) - TRUNC(UPDATE_DATE) > :v_param';
EXECUTE IMMEDIATE v_sql USING v_param;
END IF;
END;
END PURGE_PROGRAM;
/
begin
dbms_output.enable;
purge_program;
end;
/
1 rows affected
dbms_output:
Param: 42