The situation is simple. We have to create a table that contain data on a certain variable outcome. Normally, one would like to create a field in the table and add a bunch of records that relate to that outcome. However, the request is to create a new table for that. Examples might be a new table for a certain month, certain country etc.
Reluctantly, we agree and we create a procedure that create a new table for each occurance of a value.
The code looks like:
set serveroutput on
create or replace PROCEDURE SP_VEXISTABLA(TAAK IN VARCHAR)
SELECT COUNT(*) INTO TELLER FROM USER_TABLES WHERE TABLE_NAME = 'D' || TAAK;
IF (TELLER >0) THEN
execute immediate 'DROP TABLE D' || TAAK;
stmt := q'!CREATE TABLE D!' ||taak|| q'! AS
FROM ( SELECT DISTINCT ding, mandant, rest
where trim(ding)=trim('!' ||taak||q'!') and mandant=309
ORDER BY dbms_random.value)
rownum <= 2)!';
execute immediate stmt;
The code to run this is as follows: