N9ig69L5

1name
2=======
3table_1
4table_pineapple
5table_3
6	
7delete from table_N;
8	
9create table tables as 
10    select level id, 'table_'||level name
11    from dual connect by level <= 4;
12
13select
14    'drop table '||name||';'||chr (10)||
15    'create table '||name||' as select '||id||' id '|| 
16    'from dual connect by level<='||(id*10)||';'
17from tables
18where id < 4;
19	
20declare 
21    rows number;
22    function deleteTableIfExists (name varchar2) return number is
23        tableNotExist exception; 
24        pragma exception_init (tableNotExist, -942);  
25    begin
26        execute immediate 'delete from '||name;
27        return sql%rowcount;
28    exception when tableNotExist then return -1;
29    end deleteTableIfExists;
30begin
31    for r in (select * from tables) loop
32        rows := deleteTableIfExists (r.name);
33        if rows >= 0 
34        then dbms_output.put_line (r.name||' '||rows|| ' rows deleted'); 
35        else dbms_output.put_line (r.name||' does not exists');
36        end if;
37    end loop; 
38end;
39/
40
41table_1 10 rows deleted
42table_2 20 rows deleted
43table_3 30 rows deleted
44table_4 does not exists
45
46PL/SQL procedure successfully completed.