APEX_UTIL.JSON_FROM_SQL No Rows Bug + Fix

I ran into an issue yesterday using APEX_UTIL.JSON_FROM_SQL with a query that returned no rows. This function is used in AJAX calls to return the results of a query as a JSON object.

To replicate this issue you can do the following in APEX:

1- Create an On Demand Application Process

-- AP_JSON_TEST
DECLARE
  v_sql VARCHAR2 (4000);
BEGIN
  -- Note: This query is meant to return no rows
  v_sql := 'SELECT ename FROM emp WHERE 1 = 2';
  
  -- Print JSON result set
  apex_util.json_from_sql (v_sql);
END;

2- Run the following JS code(easiest using firebug)

  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_TODO_DEL',$v('pFlowStepId'));  
  vReturn = get.get(); 

At this point if you're using FireBug you'll notice that "sqlerrm:ORA-06502: PL/SQL: numeric or value error" now appears in the console window.

At first glance it appears that you have something wrong with your Application Process or with your query. After some digging around I finally realized that it's a bug with APEX_UTIL.JSON_FROM_SQL.

The good news is that it's really easy to fix. All you need to do is catch any exceptions and return a JSON object with no rows:

-- AP_JSON_TEST

DECLARE
  v_sql VARCHAR2 (4000);
BEGIN
  -- Note: This query is meant to return no rows
  v_sql := 'SELECT ename FROM emp WHERE 1 = 2';
  -- Print JSON result set
  apex_util.json_from_sql (v_sql);
  
-- *** FIX ***  
EXCEPTION
  WHEN OTHERS THEN
    HTP.p ('{"row":[]}');
END;