This programme uses PLSQL to select a set of 1000 records per item. Each item is calculated from a cursor that generates the items for which a set of records is calculated.
So the programme can be written in pseudo code as:
- For i:=1 to NumberItem
- calculate set
- end Loop
The programme looks like:
create or replace PROCEDURE SP_VEXISTABLA2
IS
stmt varchar2(1000);
TELLER NUMBER(3);
--Declare the cursor
CURSOR Product IS
select distinct "Steering View Deal ListOfValue" from DCL_FINAL_OUTCOME;
Target DCL_FINAL_OUTCOME."Steering View Deal ListOfValue"%TYPE;
begin
stmt:= 'drop table DCL_DT_CORE_DAX0329_ALL';
SELECT COUNT(*) INTO teller FROM user_tables WHERE table_name = UPPER('DCL_DT_CORE_DAX0329_ALL');
IF (teller>0) THEN
execute immediate stmt;
END IF;
stmt:= 'create table DCL_DT_CORE_DAX0329_ALL as select * from DCL_FINAL_OUTCOME where 1=0';
execute immediate stmt;
OPEN Product;
--Begin the Indefinite LOOP
LOOP
--Retrieve the CURSOR data into the holding object
FETCH Product INTO Target;
dbms_output.put_line('Verwerkt product: '|| Target);
--Test whether a record was found
EXIT WHEN Product%NOTFOUND;
stmt := q'!INSERT INTO DCL_DT_CORE_DAX0329_!' ||'ALL'|| q'!
SELECT DISTINCT * from
(select * FROM DCL_FINAL_OUTCOME
where trim("Steering View Deal ListOfValue")=trim('!' ||Target||q'!') and mandant=309
ORDER BY dbms_random.value)
WHERE
rownum <= 100
UNION ALL
SELECT DISTINCT * from
(select * FROM DCL_FINAL_OUTCOME
where trim("Steering View Deal ListOfValue")=trim('!' ||Target||q'!') and mandant!=309
ORDER BY dbms_random.value)
WHERE
rownum <= 900!';
dbms_output.put_line(stmt);
execute immediate stmt;
END LOOP;
--Close the CURSOR
CLOSE Product;
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Oops foutje');
dbms_output.put_line('SQL Code '||SQLCODE);
dbms_output.put_line('Message '||SQLERRM);
end;
/