1

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?

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
sandy
  • 243
  • 2
  • 7
  • 19
  • 3
    For historical reasons, the Oracle RDBMS treats the empty string as NULL. So those NVL() calls won't do anything meaningful. – APC Dec 30 '12 at 13:24

1 Answers1

7

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;
Codo
  • 75,595
  • 17
  • 168
  • 206
  • i guess i missed out on something, Will look into it and update. Sometimes few eye-openers like this are very advantageous. Thanks Codo. – sandy Dec 30 '12 at 13:12
  • I got my issues resolved, but i have something ringing in my mind still. Even if the whole row is missing, is there no way that i can handle and assign null values to it instead of getting a DATA NOT FOUND error and without handling in the Exception block either. I want to know if there is any other workaround? – sandy Dec 30 '12 at 16:53
  • How to restrict your above query to return just 1 row. Add the statement "and rownum < 1" at the end of the query doesn't affect the result. I still get multiple rows as output. – sandy Dec 31 '12 at 07:11
  • Write it like this: `SELECT * FROM ( SELECT ... t.name = 'W6' ) WHERE ROWNUM <= 1` – Codo Dec 31 '12 at 07:49
  • i used SELECT (SELECT NVL(t.A,'UNKNOWN'), NVL(t.B,'UNKNOWN'), NVL(t.C,'UNKNOWN') FROM DUAL LEFT JOIN SAMPLE_TABLE t ON t.name = 'W6') INTO A1, B1, C1 FROM DUAL WHERE ROWNUM <= 1; and i get PL/SQL: ORA-00913: too many values. How to re-phrase the query? – sandy Dec 31 '12 at 09:33
  • The following statement looks to work correctly. SELECT NVL(t.A,'UNKNOWN'), NVL(t.B,'UNKNOWN'), NVL(t.C,'UNKNOWN') into A1, B1, C1 FROM DUAL, SAMPLE_TABLE t where t.name = 'W6' AND ROWNUM = 1; Please let me know if the query is correct and can be used. Looks like there was a bug as reported and addressed in http://stackoverflow.com/questions/6603968/rownum-in-the-join-condition – sandy Dec 31 '12 at 10:04
  • Looks like there is still a problem, it doesn't handle the DATA NOT FOUND EXCEPTION. The GHOST has come to haunt again. – sandy Dec 31 '12 at 10:41
  • @Sandy your last comment with a query is doing an cross join between `dual` and `sample_table`. Luckily `dual` only has one row. Since you do not use any values from `dual` the results of that query will match the query you started with. Which is why the data not found exception is back. – Shannon Severance Dec 31 '12 at 11:00
  • @ShannonSeverance - I want all of those to work at one go, a select into with a rowlimit and handle the data not found exception without using the exception block. Is this literally possible? – sandy Dec 31 '12 at 11:06
  • @sandy: I've updated my answer again. The last query should always return exactly one row. – Codo Dec 31 '12 at 13:29