From the error log, its clear that the code in the nested block is not getting executed.
Why should it be executed?
With the above you just create the procedure and not execute it, that alone will not create the table for you.
Pre-create the table or use dynamic SQL so the compile-time validation will not complain about the missing table.
Ok as you have stated this code is for DEMO purpose I am going to proceed further and fix your code. If you are going to use it for any actual production purpose then its a bad idea and you should not do it. Also do not use EXCEPTION WHEN OTHERS the way you have used. Its always certainly a bug. Always WHEN OTHERS exception must be followed by RAISE. The issue is with your insert statement. The table actually does not exist when you are compiling the code. So you need to make your insert statement as Dynamic SQL so that it is not validated during compilation. Like this. SQL> create or replace procedure schema_tab_count 2 as 3 c number; 4 begin 5 declare 6 table_exist exception; 7 pragma exception_init (table_exist, -00955 ) ; 8 begin 9 execute immediate 'create table schema_tab_cnt(table_name varchar2(30),count number(10))'; 10 exception 11 when table_exist then 12 execute immediate 'truncate table schema_tab_cnt'; 13 end; 14 15 for i in (select * from tab) 16 loop 17 execute immediate 'select count(1) from "' || i.tname || '"' into c; 18 execute immediate 'insert into schema_tab_cnt values(:1,:2)' using i.tname, c; 19 end loop; 20 21 commit; 22 end; 23 / Procedure created. SQL> exec schema_tab_count PL/SQL procedure successfully completed. SQL> select * from schema_tab_cnt; TABLE_NAME COUNT ------------------------------ ---------- BIN$+4sdomixCCXgRAAUTz6XRg==$0 12 BIN$+4sdomiyCCXgRAAUTz6XRg==$0 2 BIN$/BgKoshic2jgRAAUTz6XRg==$0 500000 DEPT 4 EFF_RATE_TMP 0 EMP 11 E_MAIL_LOG 0 LOGGER 0 SCHEMA_TAB_CNT 8 TEST_MIG 2 10 rows selected. SQL>
To resolve this compilation error convert insert query to dynamic as below:
create or replace procedure schema_tab_count as
pragma exception_init(Table_exist, -00955);
execute immediate 'create table schema_tab_cnt(table_name varchar2(30),count number(10))';
when Table_exist then
execute immediate 'Truncate table schema_tab_cnt';
for i in (select * from tab) loop
execute immediate 'select count(1) from "' || i.tname || '"'
EXECUTE IMMEDIATE 'insert into schema_tab_cnt values(''' || i.tname ||
''',' || c || ')';
when others then
I've created a procedure which will populate teh tables and their counts in a schema from where you are running this procedure.Its for demo purpose ...
Right; it demonstrates some of the reasons why creating tables in PL/SQL is such a bad idea.
If you create the table dynamically, then all references to the table in that PL/SQL code have to be dynamic, too.
As others have said, lose that EXCEPTION block. Oracle's default exception handling is much better. Don't write extra code that only makes things worse.