Tuesday, May 26, 2009

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;

9 comments:

  1. Good catch! Thanks!
    I am used to wrap all apex_util functions so those bugs won't affect me too much.

    ReplyDelete
  2. So what is the bug exactly?
    You got a 6502 but don't explain why it happened. Is it only when the SQL-Statement returns no rows?

    Peter

    ReplyDelete
  3. Martin,

    It should either raise a NO_DATA_FOUND exception or return NULL and it should be documented either way. An ORA-06502 exception doesn't make much sense... Good catch.

    Regards,
    Dan

    ReplyDelete
  4. Hi Peter,

    The ORA-06502 error is occurring when no rows are returned in the query. As Dan mentioned the ORA-06502 doesn't make much sense for NO_DATA_FOUND...

    Martin

    ReplyDelete
  5. Hi Martin,

    I have filed a bug for that problem.

    Regards
    Patrick

    ReplyDelete
  6. Has anyone found a way to actually return the JSON into a variable, rather than print it? I wanted to send the JSON to another app via an HTTP POST, but json_from_sql() strangely doesn't return a value.

    ReplyDelete
  7. Hi Chris,

    json_from_sql() returns a JSON object representing the data set from the query. You can then send the JSON object to another application if necessary.

    Martin

    ReplyDelete
  8. Hello Everyone,

    Please help me. When I try to see the reports, applying Filter option. I can see the proper output. But as soon as I download the filtered report in CSV format. The Downloaded report has no effect of filter. Please help me out of this.

    Raj

    ReplyDelete
  9. Hi Raj,

    This may be a good topic to post on the APEX forums or http://apex.stackexchange.com

    Martin

    ReplyDelete