This discussion is archived
2 Replies Latest reply: Jan 21, 2013 1:20 PM by Solomon Yakobson RSS

Why I got PLS-00103 error for this procedure?

lxiscas Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points