Hi
I have a requirement to create a JSON object from a SQL statement. I've looked at using APEX_UTIL.JSON_FROM_SQL, which is the obvious solution. However, I need to add extra information to the object.
For example, APEX_UTIL.JSON_FROM_SQL('SELECT id, name FROM person') would return
{"row":[{"id":"1","name":"Tom Smith"},{"id":"2","name":"Joe Bloggs"}]}
if I only had two rows in this table.
However, I want to return something like:
{"totalrows":2,"row":[{"id":"1","name":"Tom Smith"},{"id":"2","name":"Joe Bloggs"}]}
As I see it I have two options. Modify the information returned by JSON_FROM_SQL which seems quite messy. Or my preferred option, to write a procedure that will return the data in the format I'm interested in. Then if I decide that a different format would have been the better option, I just need to change my procedure.
But I've been going round in circles trying to work out how to write my own code to do this. I'd like to be able to pass in a select statement and the procedure does everything else. This would involve using dynamic SQL to execute the SELECT statement, as this will be called from a lot of places all with different select statements, but I don't know how many columns will be in the select statement to know what sort of variable to store the results in.
If anyone has done something similar or can offer some advice I'd really appreciate it. I'm sure I'm just missing the obvious.
Thanks
Sara