stored procedures - Return data to from multiple queries in a single structure in Oracle -

I have to create a screen, which contains data from many different tables using complex additions and so on. Call the UI to get only one service data, which will be returned in JSON.

These are the approaches to my mind.

1. Perform many queries at the service level and populate an object and return the JSON object.
2. Create a template table, populate the data needed to update the original data, and query the floating table when the screen above appears.

Both approaches for me are not concrete I do not want to call many database calls at the level of service or want to create a temporary table for this purpose. Instead, I'm thinking of performing a stored procedure that will execute more than one question and return a custom structure, which can be mapped to a Java object which will be returned to the UI layer in return.

Is this possible?

You have several options, depending on the capabilities of your service layer, your DB layer and your DB Developer ...

1. Create a stored procedure with multiple output sys_refcursor parameters; You pass the required data through the cursor in your service layer; Service layer is created to make JSN object and sent to its GUI.
2. Create a stored procedure with a single output sys_refcursor parameter, which will have nested refractionar columns that you need with the basis of; Create the JSON object from the service layer and send it to your GUI.
3. Create a stored procedure that creates a complete complex structure in XML; The service layer transfers XML to JSON and sends it to your GUI.
4. Create a stored procedure that creates a complete complex structure in JSON (Oracle 12.1.0.2) and your GUI through the service layer.
5. Create a view that creates a complex structure in XML; You have a service-level query query to require a special line and need to convert XML to JSON; ...