Call oracle stored procedure from BIRT

Problem
------------
One lengthy report executes for long time and it times out. Mostly time out happens when exporting report in excel, works fine in browser view and export  to PDF.

Cause
-----------
When report is exported to excel, BIRT report executes once again and generates whole report and loads into memory. Probably below sequence would be followed

• Execute report logic
• Load all the rows in memory
• Pass the result set to excel emitter
• Emitter writes it to excel
• OutputStream writer writes to file

Whole process takes a long time to complete the operation "export report to excel". Connects times out and browser terminated the connection and export aborts. One error message in the log "Connection aborted by software on your machine".

Solution
-------------
Follow the steps below in sequence

1. Write stored procedure with the whole report logic into it (SQL of all the datasets). Make sure there is a return statement in the end procedure even though it doesn't return anything
2. Generate the result set and write it in a temp_table in the database
3. Write one more dataset in BIRT to fetch result from temp_table and publish it on the report
4. Writing report logic in stored procedure will bring down the report execution time and export will be successfull
5. Sample code of how to call stored procedure from BIRT
   
   Open method
   -------------------------
   

   maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());

   maximoDataSet.open();

   
   

   var sqlText = new String();

   
   

   　

   // Add query to sqlText variable.

   
   

   sqlText = "call myproc('PARAM1', 'PARAM2')"

   // Include the Maximo where clause

   //+ " where " + params["where"]

   ;

   　

   maximoDataSet.setQuery(sqlText);
   
   
   fetch method
   ----------------------
   

   if (!maximoDataSet.fetch())

   return (false);

   
   

   　

   return (true);
   
   Hope this helps someone, difficulties implementing .. contact me :-)