oracle-databaseplsql

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

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?

Solution

  • 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.