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

Answers


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.

Need Your Help

Child window with maximize and close buttons

javafx-2 parent-child

Still working on learning here ... I'm trying to make an application window (stage) into which I can call child windows. The parent naturally comes with minimize, maximize and close (x) buttons, bu...

C++ Event signal and background thread

c++ multithreading events visual-c++

Here is the issue that I have in c++ code implementation.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.