I had occasion on stackoverflow.com to work out an approach I'd long used:
WHENEVER SQLERROR EXIT FAIL ROLLBACK
set echo on
COLUMN programstartdate FORMAT A45
column column_value format a45
set serveroutput on
<<REINITIALIZE>> BEGIN
FOR DOIT IN (
SELECT 'DROP TABLE ' || TABLE_NAME AS CMD
FROM USER_TABLES WHERE TABLE_NAME = 'WFDISPLAYMGMT')
LOOP
EXECUTE IMMEDIATE DOIT.CMD;
DBMS_OUTPUT.PUT_LINE ('Dropped test table via command: ' || doit.CMD);
end loop;
dbms_random.seed('This doesn''t quite feel random until I ' ||
'add the microseconds ' || to_char (systimestamp, 'D FF9'));
END REINITIALIZE;
/
CREATE OR REPLACE TYPE DATELIST AS TABLE OF date;
/
show errors type datelist
CREATE OR REPLACE FUNCTION GETDATES (HOWMANY IN NUMBER) RETURN DATELIST
IS
TO_RETURN DATELIST := datelist();
BEGIN
for stepback in 1 .. howmany
loop
to_return.extend();
TO_RETURN(stepback) := trunc(sysdate - stepback);
end loop;
RETURN TO_RETURN;
END GETDATES;
/
show errors function getdates;
select * from table(cast (getdates (7) as datelist));
create table WFDISPLAYMGMT as
select
TRUNC(dbms_random.value (50, 10000)) as bogus_id,
column_value as programstartdate
from (select rownum as row_no, column_value from table(cast (getdates (30) as datelist)))
where abs (mod (row_no, 3)) = 1;
commit;
select count (*) as generated_rows from WFDISPLAYMGMT;
select *
from WFDISPLAYMGMT
where programstartdate in (
select * from table (cast (getdates (15) as datelist))
);