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.

First of all what version of Oracle are you using?
Second what jobs are running on Oracle. What are you using to submit
these "jobs". Are you executing a sql script at a certain time, etc.
Third - if your on 10g look into DBMS_SCHEDULER.CREATE_PROGRAM CALLS,
ETC. This will allow you to create program anonymous PL/SQL block,
stored procedures, or external executable file, Create custom schedule
and thus create job instance, etc.......

Bob Trautman
Oracle Certified DBA

"Mr Bob" <bob.trautman (AT) gmail (DOT) com> wrote in
news:1147049785.882576.249440 (AT) g10g2000cwb (DOT) googlegroups.com:

We are on 9i but on Solaris. This is .NET so I really need an NT
solution. I'd already thought of DBMS JOBS

The real key is using procs for reporting (I've done tons of reporting
systems, never used procs always dynamic sql passed to the oracle
engine). Procs for OLTP fine I've done enough of those including some
..NET. The real issue I see is constructing the dynamic sql and passing
the complex parameters into the proc. Sometimes the user sends in one
parameter for a particular report. At other times for the same report 3
or 4 will be sent in and one or two of them can be a list of variables.

Got anythoughts on tha

Larry Dooley <larrydooley (AT) yahoo (DOT) com> wrote in
news:Xns97BCDB63C30CBlarrydooley (AT) 216 (DOT) 196.97.142:
SQL is SQL; regardless of source (.NET, JSP, Oracle Forms, etc.)
It is just A Small Matter Of Programming (SMOP).

On Mon, 08 May 2006 01:45:30 +0000, ianal Vista wrote:

True. But, as time goes on, I'm realizing that, while 'SQL is SQL', the
'consequences of SQL' may be different on different platforms.

That seems often to require a MMOT (Minor Matter of Thinking} to be done
before SMOP or it is possible to end up with a SMUDGE (Small Mound of
Unuseable and Deleterious Generic Excrement)

The OP's project certainly sounds like a project I'd been asked to
invest in, done by a little local company. Worked like a charm in the
prototype environment, too. Scaling, due to the dynamic SQL, proved to be
a bit of a challenge, for all the reasons cited by Tom Kyte in his book
"Expert Oracle Database Architecture: 9i and 10g Programming Techniques
and Solutions" (http://apress.com/book/bookDisplay.html?bID=10008).

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

HansF <News.Hans (AT) telus (DOT) net> wrote in
newsan.2006.05.08.02.21.37.213580 (AT) telus (DOT) net:

fact, it's a pretty nice solution. But we wrapped a more procedual
language around oracle to build the sql and then just pass it to Oracle.
Our solution handles all the variations on the parameters since it's an
html post we can easily parse the value pairs, if we get multiple value
for the same parameter then we've got a list and we just keep appending
it until we get a new parameter. No big deal now. Also, it generic
enough for us use on any of our reports. Because of the complexity of
the datasource (a packaged solution) we have reports that have 4 unions
against different sets of tables. In each union the actual column that
we are going against can be different (sometimes a function). We handle
that as well.

My heartburn is doing all of this in a proc. This is what I want feed
back on. Potential solutions problems etc. Thnaks