Re: question on oracle to combine with reports software

Originally posted by J
> Hi all ,
>
> I am now working in a company and they are using crystal report
> with
> oracle database. They told me not to draw data directly from all
> tables into
> the report. Rather, on application level, they trigger a stored proc
> to
> insert all the retrieved data into another "tailor made" table
> for report
> and then forward to crystal report to draw the data from the
> table. They
> said that they can customize the logic better and run faster.
> They sounds
> right but I am not sure if this trivial approach is the best practice.
> Please advise.
>
> Thanks
>
> Perseus

It probably makes sense if you are trying not to overload your production server with complex queries from Crystal Reports. Now, the stored procedure approach sounds a little "barbaric" in the sense that it's cumbersome to write and maintain. And believe it or not, PL/SQL is not the most efficient way to access data in Oracle - plain Oracle SQL or dblink is a lot more performant. Why not look at ETL tools (like Sunopsis or Informatica) to do that work? They present the additional advantage or offloading the processing involved in the transfer of the data from the production DB to a proprietary engine (in Informatica) or the other Oracle server (in Sunopsis) that you use as your target - then as the source for the queries. I believe that certain ETL tools can even use native bulk transfer utilities like dblink. And you may also want to prepare the data for the Crystal report queries as you transfer it (as in building a data warehouse).

--
Posted via http://dbforums.com