Bypass no data execution Oracle PL/SQL

Question

I am trying to check for two kinds of authorization level for entries in the menu to display according to different levels given to each user.

But the no data exception in oracle sql is preventing me from checking the second table for authorization.

Here is my code:

declare 
  v_count number;
  v_name varchar2(255);
begin
  select DASHBOARD into v_count from RCM_ADMINISTRATOR where USER_NAME = :APP_USER;
  select MANAGER into v_name from RCM_ADMINISTRATION_TEAMS where MANAGER = :APP_USER;

  if v_count >= 1 then
    return true;
  end if;
  if v_name is not null then
      return true;
  end if;
  if v_count = 0 then
   return false;
  end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
 return false;

end; 

Is there a way to make it so that if I get no data from the first query, I can still run the second query?

Answer 1

There are several issues with your code. You are using an anonymous block and returning a value, which is not possible. You can write a function which returns a BOOLEAN value(True or False) However, it is of no use because as per Oracle documentation http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#CJACJGBG.

You cannot insert the values TRUE and FALSE into a database column. You cannot select or fetch column values into a BOOLEAN variable. Functions called from a SQL query cannot take any BOOLEAN parameters. Neither can built-in SQL functions such as TO_CHAR; to represent BOOLEAN values in output, you must use IF-THEN or CASE constructs to translate BOOLEAN values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

So In the code, VARCHAR2 is used as RETURN argument.

Pass APP_USER as function argument instead of using a bind variable.

For your condition that each query must execute irrespective of exceptions, you can place it in separate BEGIN..END blocks.

Once you have used RETURN from a stored procedure, you cannot jump back to the rest of the code after exception as you are trying to achieve. Instead, store the value in a variable exit_code and RETURN using the code before the last END to return eventually with an exit code if other conditions are not satisfied.

CREATE OR REPLACE FUNCTION f_test_ret(p_app_user IN VARCHAR2)  
  RETURN VARCHAR2
AS
  v_count   NUMBER;
  v_name    VARCHAR2(255);
  exit_code VARCHAR2(6);
BEGIN
  BEGIN
    SELECT DASHBOARD
    INTO v_count
    FROM RCM_ADMINISTRATOR
    WHERE USER_NAME = p_app_user;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    exit_code := 'FALSE';
  END;
  BEGIN
    SELECT MANAGER
    INTO v_name
    FROM RCM_ADMINISTRATION_TEAMS
    WHERE MANAGER = p_app_user;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    exit_code := 'FALSE';
  END;
  IF v_count >= 1 THEN
    RETURN 'TRUE';
  END IF;
  IF v_name IS NOT NULL THEN
    RETURN 'TRUE';
  END IF;
  IF v_count = 0 THEN
    RETURN 'TRUE';
  END IF;
  RETURN exit_code;
END;
/

Answer 2

This is a very common problem, with many solutions.

One option is to put a BEGIN / EXCEPTION / END block around each SELECT INTO that you're trying to do.

declare 
  v_count number;
  v_name varchar2(255);
begin
  BEGIN
    select DASHBOARD into v_count from RCM_ADMINISTRATOR where USER_NAME = :APP_USER;
  EXCEPTION when no_data_found THEN v_count := null;
  END;
  BEGIN
    select MANAGER into v_name from RCM_ADMINISTRATION_TEAMS where MANAGER = :APP_USER;
  EXCEPTION when no_data_found THEN v_name := null;
  END;

  if v_count >= 1 then
  ...etc...

I often find it easier just to use MAX:

declare 
  v_count number;
  v_name varchar2(255);
begin
  select max(DASHBOARD) into v_count from RCM_ADMINISTRATOR where USER_NAME = :APP_USER;
  select max(MANAGER) into v_name from RCM_ADMINISTRATION_TEAMS where MANAGER = :APP_USER;

  if v_count >= 1 then
  ...etc...

It's a lazy technique, and if you aren't careful, it can disguise ORA-01422 errors (exact fetch returns more than requested number of rows). But if you know your query will only return 0 or 1 row, then it will work fine.