CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
sandy
sandy

Reputation: 243

suppress oracle data not found exception

I have a unique scenario where-in i need multiple column values to be put into multiple variables. The problem i am facing is that while one column value is present the others needn't be present, hence i end up with DATA NOT FOUND exception, while i want to suppress it and put empty values into the remaining variable. 

 select nvl(A,''), nvl(B,''), nvl(C,'') 
   into A1, B1, C1  
   from SAMPLE_TABLE 
  where name ='W6';

Value of A in the table can be 'hello', Value of B is null and Value of C is null in the table.
When the statement is executed inside the body of a stored proc i do not want the DATA NOT FOUND Exception, instead i want A1 to have the value 'hello', B1 as '' and C1 as ''. I will be running this dynamically and the where condition keeps changing, hence i do not want to open those many cursors either. Can anyone please let me know how i can accomplish the same?

Upvotes: 1

Views: 3575

Answers (1)

Codo
Codo

Reputation: 78915

Your analysis isn't quite correct. You only receive the DATA NOT FOUND error if the whole row is missing, i.e. the WHERE condition name ='W6' doesn't select any rows.

To avoid the error, you can use exception handling:

BEGIN
    select A, B, C 
    into A1, B1, C1  
    from SAMPLE_TABLE where name ='W6';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        A1 := 'hello';
        B1 := NULL;
        C1 := NULL;
END;

Update:

If you want to select a NULL values even if the WHERE condition matches no row, then you can try the following query:

SELECT t.A, t.B, t.C
FROM DUAL
LEFT JOIN SAMPLE_TABLE t ON t.name = 'W6';

Update 2: Query with exactly one row:

This query should always return a single row:

SELECT A, B, C
  INTO A1, B1, C1
FROM (
   SELECT t.A, t.B, t.C
   FROM DUAL
   LEFT JOIN SAMPLE_TABLE t ON t.name = 'W6'
) x
WHERE ROWNUM <= 1;

Upvotes: 7

Related Questions