Executing multiple queries in a single DB Call - SQL

I'm 开发者_高级运维creating pdf reports with Data retrieved from Database (Oracle). For each report I'm making a DB Call. I want to optimize the call for multiple reports ( can have max of 500 reports). In current scenario, I am making 500 DB calls and this results in timeout of the Server.

I'm looking for solutions and answers.

1. Can I pass a list of data as input to a query ? (The query required 2 inputs.)

2. The entire set of data retrieval should happen in 1 DB Call not 500 separate calls.

3. The response should be accumulated result of 500 inputs.

Please suggest ways to solve or directions to the solve the issue ?

It is a Java based system. The DB call is from a Web App. DB : Oracle.

Thanks!!

If you want to get the data for an arbitrary number of "reports" in a single database call, then I would imagine you need to be calling a stored procedure that returns a very large nugget of XML or JSON text that you can then parse and display in your application. Oracle has built-in functions for constructing XML, and JSON is pretty easy to structure yourself (though I believe a 3rd party PL/SQL JSON package may be available).

there are a few ways of combining results from multiple queries.

1. UNION ALL -- lets you literally combine results between query1 UNION ALL query2
2. Make 1 more general query. -- this is the best answer if it can be done.
3. Join Sub queries and print the data horizontally if you can join them. select a., b. from (querya) a join (queryb) b on (id)
4. There are probably other ways as well. Such as a stored procedure etc.