Handy PLSQL programme

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;
 /