Check access on a table before running select

Question

I have 3 functions in PL/SQL. Function1 checks for whether the user has SELECT access on TABLE A,B,C using user_tab_privs . If the user does not have access on any tables then store the missing count. Function2 selects some data from Tables A,B,C. And finally function3 checks if the count value in Function1 >0 . If this condition is TRUE then raise exception and print count else proceed to Function2 and output. When the user has any missing privileges then script throws ORA-00942: table or view does not exist instead of raising exception.How to output raise_application_error when the user has any missing access instead of ORA-00942.

Here is the snippet of the final function.

function function3 return clob is
..
    BEGIN
        missingcount := function1();
       if missingcount >0 then
            error := 'Failed ' || missingcount ');
            raise_application_error(-20101, error );
        ELSE
           fun2output := function2 ();
        end if;
    END function3;
BEGIN
 final := function3();
 dbms_output.put_line(final);
END;

How to output raise_application_error when the user has any missing access instead of ORA-00942.

Answer 1

You need exception handling. Trap the exception you want and raise whatever you'd like:

DECLARE
  tab_does_not_exist exception;
  PRAGMA EXCEPTION_INIT(tab_does_not_exist,-00942);
BEGIN
  do-something-that-might-fail;
EXCEPTION
  WHEN tab_does_not_exist THEN
    RAISE_APPLICATION_ERROR(-20101,'my custom error message');
END;

There are a couple of exceptions that are pre-named that you don't have to use the pragma to define. Like DUP_VAL_ON_INDEX, NO_DATA_FOUND or TOO_MANY_ROWS and a half dozen others. But most exceptions don't have names so this is how you give them names so you can act on just those exceptions. Alternatively, you can catch all exceptions with WHEN OTHERS and refer to the SQLCODE function inside it to decide what to do:

BEGIN
  do-something-that-might-fail;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942
    THEN
      RAISE_APPLICATION_ERROR(-20101,'my custom error message');
    END IF;

    RAISE; -- re-raise anything else
END;