.Net Reporting against Oracle

[Larry Dooley]

Here's my issue. We've decided to replace a very critical (without it
the business would lose lots of money) departmental reporting system
with a built from scratch system based on .NET. The key component is a
datagrid (it really turned the CIO's head).

The issue is that the data source is Oracle. We've got a very complex
reporting system that produces dynamic sql and sends it to oracle and
takes back the result set and displays it. Each report has at least 3
filters and some more than a dozen. Each filter can either be a single
item, a list of 1 to N items or left blank. The idea is to push this
into Oracle stored procedures and get back a refcursor. The
filters/parameters effect not only the where clause, but can effect the
select clause, the from clause (what tables are queried) and when either
the from clause or select clause are effected the group by clause is
effected. This is a complex database (not large by data wharehouse
standards) with indexes and structure that are not friendly to
reporting. Speed of the reports is a critical issue. It's something
we've fought pretty sucessfully in the old system.

We've also got to have a batch component. That is a number of reports
need to run overnight and be available first thing in the morning. This
can't be just a single job that runs a bunch of reports. We need each
report to run separately. Oh and it needs to not start before certain
jobs are finished on the Oracle database.

Oh we've got a schedule of six months - nine months max and the clock is
ticking.

For the first issue. Has anyone done something similar. If so any key
problems with this (forget buy crystal, cognos etc. It's not cost they
are rounding error in our bottom line, but that decision is pretty set
also forget a data wharehouse - no time)

Second issue - does anyone know a good scheduler that will integrate
well with a .NET solution.

Thanks in advance.

 

[Robbe Morris [C# MVP]]

Why is Oracle being the data source an issue? If you
properly layer your code, you'll hide the Oracle provider
specific code way below the reporting engine. You
would expose the returned data in classes, DataTables,
or DataSets to the reporting engine and it would
have no knowledge of which database brand
was being used because none of these are specific to Oracle.

..NET executables can be scheduled just like any other
executable.

You could always write a windows service in .NET.
That's pretty simple actually...

 

[Larry Dooley]

.NET executables can be scheduled just like any other
executable.

You could always write a windows service in .NET.
That's pretty simple actually...

Actually the plan from the .NET people is to get Oracle procs to return
the data in a refcursor we are going native Oracle. No ODBC connections.

I'm not happy about writing anything from scratch if I can get something
already done.

This has enough new stuff already to increase the risk. This blows
people lose their jobs at least. A screw up can easily cost millions.

So once again. The key question is .NET reporting directly against
Oracle using oracle stored procedures and refcursors.

 

[tomb]

Oracle has, or at least had, its own data connection addon for .net, and
it worked beautifully in my project. I was using Oracle 9, and .net
2003. Oracle's documentation was quite complete, and showed very
clearly how to interact with a refcursor, stored procs, and parameters
from within .net. After that, it's pretty much how Robbe described
it. My question though, if they want to go truly native Oracle, why
don't you use Oracle's dev tools?
Anyway, if you have trouble locating what you need, you are welcome to
email me directly. And if you give me a couple of day, I think I still
have the code from that project. (e-mail address removed)

Tom