CODEWITHSUNDEEP
oracle-databaseplsql
Roman
Roman

Reputation: 13

Query in WHEN NO_DATA_FOUND clause. Bad practice?

Developers at our job are constantly using the follow construct:

BEGIN    
  SELECT field1 
  INTO l_field1
  FROM table1
  WHERE field2 = 'some value'
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
     SELECT field2 
     INTO l_field2
     FROM table2
     WHERE field3 = 'some value'
   ...
END;

What basically means - if you don't find the record in the first table then go look in the second table.

I have a strong feeling that this is wrong and is a bad lazy practice. But how would you approach it? Would you first get the COUNT in the table1 and if number of rows is zero then go and look in table2?

Upvotes: 0

Views: 544

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

My objection to the "SELECT-in-an-EXCEPTION" stuff is that it nests blocks which have no business being nested. My preference is to use a boolean indicator variable, similar to the following:

DECLARE
  bNot_found  BOOLEAN := FALSE;
  l_field1    TABLE1.FIELD1%TYPE;
  l_field2    TABLE2.FIELD2%TYPE;
BEGIN
  BEGIN    
    SELECT field1 
      INTO l_field1
      FROM table1
      WHERE field2 = 'some value';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      bNot_found := TRUE;
  END;

  IF bNot_found THEN
   SELECT field2 
     INTO l_field2
     FROM table2
     WHERE field3 = 'some value';
  END IF;

  -- Go thou and do good works

END;

Upvotes: 0

Moudiz
Moudiz

Reputation: 7377

if you are asking for an alternative way, you can use the below way. When No data found is considered to handle errors or warning, , not conditions. Exceptions helps you to handle errors , if you were trying to run several selects and you had an error .the exception will help you detect you check the below example. 

BEGIN   
declare cnt number(4); 
select count(1) into cnt from table1 where field2 = 'some value';
if cnt >0 then
  SELECT field1 
  INTO l_field1
  FROM table1
  WHERE field2 = 'some value'
else if cnt =0 then
  SELECT field2 
     INTO l_field2
     FROM table2
     WHERE field3 = 'some value';
end if;
   END;

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

BEGIN
   SELECT ...
   SELECT ...
   SELECT ...
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors

for more information about exception and handling errors check Oracle Documentation

Upvotes: 1

Related Questions