Oracle APEX: NO DATA FOUND when querying the APEX_COLLECTION

Question

I am querying APEX_COLLECTION view to see if a record exists and when it does not, I am getting NO_DATA_FOUND exception. To handle that I used an anonymous block:

BEGIN 

    SELECT c001, c002 INTO l_var1, l_var2
    FROM APEX_COLLECTION
    WHERE collection_name = 'TEST' AND c003='test';
EXCEPTION
WHEN NO_DATA_FOUND
    l_var1 := NULL;
    l_var2 := NULL;
END;

Is there a better way to handle this?

Answer 1

Did not find better solution to my issue besides using anonymous block and capturing a NO_DATA_FOUND exception.

Answer 2

If you just want to see if a record exists, you can use de COUNT() function. In this way, it is not necessary to use anonymous blocks or NO_DATA_FOUND excepcion. The COUNT function always returns a value.

SELECT COUNT(*)
INTO l_count
FROM APEX_COLLECTION
WHERE collection_name = 'TEST' AND c003='test';

IF l_count > 0 THEN
  -- Do something, the record exits
ELSE
  -- Do something, the record does not exit
END IF;

Answer 3

Use a predefined cursor and you won't be bothered with the NO_DATA_FOUND exception.

DECLARE
  cursor c_apex_coll
  is
    SELECT c001, c002
    FROM APEX_COLLECTION
    WHERE collection_name = 'TEST' AND c003='test';
  
  l_var1 varchar2(256);
  l_var2 varchar2(256);
BEGIN
  open c_apex_coll;
  fetch c_apex_coll into l_var1, l_var2;
  close c_apex_coll;
END;

Answer 4

If you're only expecting one matching row in the result you could use min() on all the fields, if no rows are returned then all will be null.

SELECT min(c001), min(c002) 
INTO l_var1, l_var2
FROM APEX_COLLECTION
WHERE collection_name = 'TEST' AND c003='test';