pl/sql - Is there a NVL() for empty rows

Question

With this,

set serveroutput on size 900000;
DECLARE
  test VARCHAR(255):=0;
BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);
END;

I'm having the error

"no data found"

When ID doesn't exist in database.

How can I use something like nvl() here, so I can get a default value instead of get an error?

Answer 1

You could catch the NO_DATA_FOUND Exception

DECLARE
  test VARCHAR(255):=0;
BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line ('sorry no data found :(' );
END;

More on PL/SQL error handling here: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm

To my knowledge, there is no such thing as nvl() for an empty row in PL/SQL, the NO_DATA_FOUND exception is probably the closest you'll get.

Answer 2

If appropriate you could do this as an alternative to the more usual NO_DATA_FOUND exception handling:

DECLARE
  test VARCHAR(255):=0;
BEGIN
    SELECT NVL(MAX(id),'default') INTO test FROM sku WHERE id = 1515;
    dbms_output.put_line('Result:' || test);
END;

By using MAX(ID) you are sure to get one row back, which will have a NULL when there is no data; you then use NVL in the usual way.

Answer 3

Depending on what you're doing, you could move your query to a cursor and fetch from it:

DECLARE
test VARCHAR(255):=0;

CURSOR c_test IS
    SELECT id
    FROM sku
    WHERE id = 1515
; 
BEGIN
    OPEN c_test;
    FETCH c_test INTO test;
    IF c_test%NOTFOUND THEN
        dbms_output.put_line('no data found');
    ELSE
        dbms_output.put_line('result:' || test);
    END IF;
    CLOSE c_test;
END;

Answer 4

You can't use NVL (or NVL2 or COALESCE, which are similar) here because there's no data on which these functions can operate. The following will do what you've asked:

DECLARE 
  test VARCHAR2(255):=0; 
BEGIN 
  BEGIN
    SELECT id INTO test FROM sku WHERE id = 1515;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      test := '0';
  END;

  dbms_output.put_line('Result:' || test); 
END;

but just because something is possible doesn't mean it's a good idea. The database is trying to warn about something important (specifically, that there's no data which matches your criteria) and this code ignores that warning.