2 Replies Latest reply: Jan 21, 2013 3:20 PM by Solomon Yakobson RSS

    Why I got PLS-00103 error for this procedure?

    lxiscas
      hi, guys:

      I have a question about exception syntax, I want to record the number of rows of multiple tables, but if a table does not exist, my program should be able to continue loop.
      create or replace procedure check_rows as
      
      
         cursor t1 is select table_name from all_csv
                   where table_built='Y'
                   and table_name is not null;
      
        match_count1 INTEGER;
        
      begin
      
         for n in t1 loop
         
            dbms_output.put_line(n.table_name);
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || n.table_name INTO match_count1 ;
      
            
            update all_csv
            set total_rows=match_count1
            where table_name=n.table_name;
            
            exception when table_does_not_exist then null end;  
      
         end loop;
         
         commit;
      
      end;
      I go ta syntax error as :


      PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
      of the following:
      begin case declare end exit for goto if loop mod null pragma
      raise return select update while with <an identifier>
      <a double-quoted delimited-identifier> <a bind variable> <<
      close current delete fetch lock insert open rollback
      savepoint set sql execute commit forall merge pipe

      Anyone could give me a hint?

      Thanks a lot!

      Sam
        • 1. Re: Why I got PLS-00103 error for this procedure?
          lxiscas
          I just figured out that I should use nested begin end block:
          create or replace procedure check_rows as
          
          
             cursor t1 is select table_name from all_csv
                       where table_built='Y'
                       and table_name is not null;
          
            match_count1 INTEGER;
            
          begin
          
             for n in t1 loop
             
                dbms_output.put_line(n.table_name);
                
                begin
                  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || n.table_name INTO match_count1 ;
            
                  
                  update all_csv
                  set total_rows=match_count1
                  where table_name=n.table_name;
                
                exception  when table_does_not_exist then null ;
                end;  
          
             end loop;
             
             commit;
          
          end;
          • 2. Re: Why I got PLS-00103 error for this procedure?
            Solomon Yakobson
            EXCEPTION clause must be at the end of BEGIN block. It can't start in the middle of begin lock body:
            create or replace
              procedure check_rows
                as
                    cursor t1
                      is
                        select  table_name
                          from  all_csv
                          where table_built='Y'
                            and table_name is not null;
                    match_count1 INTEGER;
                begin
                    for n in t1 loop
                      begin
                          dbms_output.put_line(n.table_name);
                          EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || n.table_name INTO match_count1;
                          update  all_csv
                             set  total_rows = match_count1
                            where table_name = n.table_name;
                        exception
                          when table_does_not_exist then null;
                      end;  
                    end loop;
               commit;
            end;
            /
            And it is not a good idea to commit in stored procedure. But main issue here is table_does_not_exist exception since it is not declared in your code. You need to declare it and use PRAGMA EXCEPTION_INIT to link it to desired error code.

            SY.