9 Replies Latest reply: Jul 2, 2014 12:55 AM by _Karthick_ RSS

    Why nested Block is not executed here???

    808794

      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 for pl/sql nested block and also I'm going to create a trigger to refresh this table. Here is my code.

       

      create or replace procedure schema_tab_count as

      c number;

      begin

           Declare

            Table_exist exception;

             pragma exception_init ( Table_exist, -00955 ) ;

            begin

             execute immediate 'create table schema_tab_cnt(table_name varchar2(30),count number(10))';

             exception when   Table_exist     then

             execute immediate 'Truncate table schema_tab_cnt';

            end;

      for i in (select * from tab) loop

      execute immediate 'select count(1)  from ' || i.tname   into c;

      insert into schema_tab_cnt values(i.tname,c);

      end loop;

      commit;

      exception when others then null;

      end;

      /

       

      Warning: Procedure created with compilation errors.

       

      SQL> sho err

      Errors for PROCEDURE SCHEMA_TAB_COUNT:

       

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      14/1     PL/SQL: SQL Statement ignored

      14/13    PL/SQL: ORA-00942: table or view does not exist

      SQL>

       

      From the error log, its clear that the code in the nested block is not getting executed.

      What is the reason and solution for this ?

      Thanks.

        • 1. Re: Why nested Block is not executed here???
          Balazs Papp

          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.

          • 2. Re: Why nested Block is not executed here???
            BluShadow

            The code is not even getting past the compilation stage let alone getting to execute.

             

            When it sees:

             

            insert into schema_tab_cnt values(i.tname,c);

             

            that table doesn't exist so it can't compile.

             

            Tables should not be created at runtime.  That is not how to design a robust database application.

            • 3. Re: Why nested Block is not executed here???
              _Karthick_
              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>
              • 4. Re: Why nested Block is not executed here???
                user4585215

                Hi,

                 

                To resolve this compilation error convert insert query to dynamic as below:

                 

                create or replace procedure schema_tab_count as

                  c number;

                begin

                  Declare 

                    Table_exist exception; 

                    pragma exception_init(Table_exist, -00955); 

                  begin 

                    execute immediate 'create table schema_tab_cnt(table_name varchar2(30),count number(10))'; 

                  exception

                    when Table_exist then   

                      execute immediate 'Truncate table schema_tab_cnt';   

                  end;

                  for i in (select * from tab) loop

                    execute immediate 'select count(1)  from "' || i.tname || '"'

                      into c;

                    EXECUTE IMMEDIATE 'insert into schema_tab_cnt values(''' || i.tname ||

                                      ''',' || c || ')';

                  end loop;

                  commit;

                exception

                  when others then

                    NULL;

                end;

                • 5. Re: Why nested Block is not executed here???
                  SomeoneElse

                  > exception

                  >   when others then

                  >     NULL;

                   

                  Ouch.

                  • 6. Re: Why nested Block is not executed here???
                    Frank Kulash

                    Hi,

                     

                     

                    808794 wrote:

                     

                    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.

                    • 8. Re: Why nested Block is not executed here???
                      808794

                      I know its not recommended to create a table here. the purpose of this code can be achieved by querying all_tables. Its just to show what we can do in pl/sql. Thanks anyway.

                      • 9. Re: Why nested Block is not executed here???
                        _Karthick_

                        808794 wrote:

                         

                        I know its not recommended to create a table here. the purpose of this code can be achieved by querying all_tables. Its just to show what we can do in pl/sql. Thanks anyway.

                         

                         

                         

                        Irony is you have shown what we should not do in PL/SQL