how handle table or view does not exist exception?

Question

I have a set of table names, let say 150. Each table have mail_id column, now I want to search one mail_id in all of the table. For that I wrote one Plsql block. When I loop through the set of table some tables do not exists so it raises an exception. I have exception handling block to handle that exception. Now I want to loop entire table even though it raise an exception? Any idea? Actually my block didn't handle that particular exception!

declare
my_mail_id varchar2(50):='[email protected]';
tmp_table varchar2(125);
type varchar_collector is table of varchar2(255);
var varchar_collector;
table_does_not_exist exception;  
PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);

begin

for cntr in (select table_name from user_tables)
    loop

    tmp_table:=cntr.table_name;
    dbms_output.put_line(tmp_table);
        for mail in (select email_address from tmp_table where lower(email_address) like '%my_mail_id%' )
        loop
            dbms_output.put_line(tmp_table);
        end loop;
    end loop;

    exception 
    when no_data_found then 
        dbms_output.put_line('email address not found');

    WHEN table_does_not_exist then 
         dbms_output.put_line('table dose not exists');

    WHEN OTHERS THEN
    --raise_application_error(-20101, 'Expecting at least 1000 tables');
    IF (SQLCODE = -942) THEN
        --DBMS_Output.Put_Line (SQLERRM);
        DBMS_Output.Put_Line ('in exception');--this exception not handled
    ELSE
        RAISE;
    END IF;
end;

Answer 1

Just handle your exceptions in anonymous block inside the loop.

DECLARE
  my_mail_id VARCHAR2(50) := '[email protected]';
  tmp_table  VARCHAR2(125);
  TYPE varchar_collector IS TABLE OF VARCHAR2(255);
  var varchar_collector;
  table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);

BEGIN

  FOR cntr IN (SELECT table_name FROM user_tables)
  LOOP
    BEGIN
      tmp_table := cntr.table_name;
      dbms_output.put_line(tmp_table);
      FOR mail IN (SELECT email_address
                     FROM tmp_table
                    WHERE lower(email_address) LIKE '%my_mail_id%')
      LOOP
        dbms_output.put_line(tmp_table);
      END LOOP;

    EXCEPTION
      WHEN no_data_found THEN
        dbms_output.put_line('email address not found');

      WHEN table_does_not_exist THEN
        dbms_output.put_line('table dose not exists');

      WHEN OTHERS THEN
        --raise_application_error(-20101, 'Expecting at least 1000 tables');
        IF (SQLCODE = -942)
        THEN
          --DBMS_Output.Put_Line (SQLERRM);
          DBMS_Output.Put_Line('in exception'); --this exception not handled
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;

Answer 2

If you're selecting from user_tables and finding that some of them do not exist then you're probably trying to query tables that are in the recycle bin (their names begin BIN$).

If so, change your query to:

select table_name
from   user_tables
where  dropped = 'NO';

You should replace your second cursor with a call to execute immediate also, constructing the query by concatenating in the table_name not just using a variable as the table name, and you might as well construct the query as:

select count(*)
from   table_name
where  lower(email_address) like '%my_mail_id%'
and    rownum = 1;

That way you'll retrieve a single record that is either 0 or 1 to indicate whether the email address was found, and no need for error handling.