json_dyn.executeList error where SQL inc %

  • Anonymous - 2012-06-14

    I'm getting an error when I'm trying to run the following code:

    JOB_LIST := json_dyn.executeList('SELECT * FROM table WHERE name LIKE ''%' || search_name_variable || '%'' ');

    I've tried various ways of putting the % in (eg using Chr(37) etc) but nothing seems to work.

    Please help…

  • Anonymous - 2012-06-14

    UPDATE - found out it's not the % but rather the somthing to do with the size/ content of the results.

    Is there a limit on the size of the results that can be returned? or on the number / type of fields allowed?


  • James Sumners

    James Sumners - 2012-06-14

    Looking through json_dyn, it looks like executeList returns a json_list type. So looking at the constructor of json_list, it is clear that a json_list is merely a table of json_value_array. So, if your query exceeds the limit of such a table (which is memory bound, I believe) then you'll get some sort of error (which you didn't specify).

    Personally, I think you'd do better to limit your result set.

  • Anonymous - 2012-06-14

    Limiting the result set is what I`ll have to do.

    I was using:

    HTP.Print( JOB.to_char );

    to output the results which was hitting the 32k limit of a VarChar2.

    I've now changed to using:


    and that works much better and outputs much larger results BUT it doesn't seem to like CLOBS.

    If the results of my SQL query include a CLOB field then I get the error:

    PL?SQL: numeric or value error: invalid LOB locator specofoed: ORA-22275
    "SYS.DBMS_LOB" line 759
    "JSON_VALUE" line 32
    "JSON_DYN" line 222

    Any thoughts? Cheers…

  • James Sumners

    James Sumners - 2012-06-14

    The same. Don't dump the complete contents of your table to the OWA output. Return something like:

    "results": [
    "result 1",
    "result 2",
    "result 3"
  • Anonymous - 2012-06-15

    So how would I include a CLOB field with the "executeList / executeObject" call?

    ( Thanks for getting back with all this by the way… :o)

  • James Sumners

    James Sumners - 2012-06-15

    Those methods are for convenience, and I bet they weren't designed with very large data sets in mind. Basically, I'd avoid using them in this scenario and write my own procedure that builds a basic JSON object prints it (e.g. `myobj.htp()`).

  • Anonymous - 2012-06-15

    Cheers James…


Log in to post a comment.