CODEWITHSUNDEEP
sqldatabaseoracle-databasecall
SKR
SKR

Reputation: 813

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!!

Upvotes: 1

Views: 680

Answers (2)

Gidon Wise
Gidon Wise

Reputation: 1916

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.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132680

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).

Upvotes: 3

Related Questions