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.
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?
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.
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…
The same. Don't dump the complete contents of your table to the OWA output. Return something like:
So how would I include a CLOB field with the "executeList / executeObject" call?
( Thanks for getting back with all this by the way… :o)
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()`).
Log in to post a comment.