I'm trying to get some JSON that has been generated in my pl/sql code to be displayed on to a browser. The package procedure is being called as a web service. I've tried a number of different methods to output the string (with no luck) including the following:
- htp.p( json.to_varchar2( master_obj.to_anydata ) ); (This produced a 404 error)
- clob_output_var := json.to_varchar2( master_obj.to_anydata ); htp.p(clob_output_var); (404 error again)
- master_obj.print(); (produced nothing)
- hand building the JSON string in a clob using the JSON format and output with htp.p
The last does work, but it was intended as a temp solution only and I wouldn't be happy leaving the code like this.
Has anyone got any ideas on how I can achieve this?
Many thanks, Patrick
I see that you didn't understand the purpose of the static functions in the JSON type. The idea was to convert the anydata type back to the oracle type, so json.to_varchar2 assumes that the anydata is actually containing a string (today I would have called it to_string). The function you are looking for is named to_char.
If you update to the latest version (0.9.0) you will see that those static functions has disapeared and a new type JSON_VALUE has replaced anydata. The purpose of JSON_VALUE is the same as the anydata type, to store any json value. However, The new type has some functions attached, that makes it much easier to work with than anydata. You can even extract the content with the to_char function. The to_char function works on JSON, JSON_LIST and JSON_VALUE.
To your question: You want to output a JSON object to mod_plsql, simply use:
BTW: The print command in JSON, JSON_LIST and JSON_VALUE omits to dbms_output - that's why you didn't see any output - 404 might mean an error in your plsql.
Thanks, that worked a treat. It all seems to be working great now.
My pl/sql package that I was calling did compile no problem and the 404 error only appeared when I switched the outputing line for the JSON string in my pl/sql. Which when "technically" correct as far as each function took in correct parameter types - and as you pointed ut my understanding of them was a bit off - they were not implemented in the correct way. Hence… 404 error.
Log in to post a comment.