Re: Start MS-excel from oracle on clientPC

Sybrand Bakker wrote:

> Comments embedded
>
> "Siebe Jongebloed" <s.jongebloed_at_chello.nl> wrote in message
> news:7mTV8.369506$yP6.14176478_at_Flipper...
> > Hi Sybrand,
> >
> >
> >
> > Since I was maybe a bit short in why I want to create such "a disaster
> > application", here a bit more.
> >
> >
> >
> > First. It is not the case that I think PL/SQL could not do the job. That
> > would just be easy programming.
> >
> > But, the case is, that "the organisation", where I need this solution for,
> > is working with lots of spreadsheets, which use formulas inside, who
> change
> > a lot and differ a lot.
> >
> >
>
> So this just means business-rules implemented outside the data-model.
> Business-rules should be implemented in the database.
> What you are basically discussing is automating chaos, which definitely will
> not resolve the real problem.
> Garbage In is Garbage Out.
> >
> > The spreadsheets are using source-data(taken over by hand) from a oracle
> > database and is supplying target data(also taken over by hand). Note: The
> > people who are responsible for those spreadsheets are the opposite of a
> > "Senior Oracle DBA".
> >
> >
> >
> > So I know there a several ways to use, for example ODBC, to atomise the
> 'by
> > hand'-thing.
> >
> > But that is just one step.
> >
> >
> >
> > In principle I would like the situation, that when Oracle-source-data
> > changes, also the Oracle-result-data would change automatically. No human
> > interaction involved.
> >
> >
> >
> > So I need a 'On-Change'-trigger on the 'Oracle-source-data', that would
> > cause excel to calculate the new Oracle-result-data.
>
> Triggers are fired on the server. Calling an external procedure from your
> trigger to start Excel, which means Excel on the server, theoretically many
> times. You do not even *want* to think about it.
> >
> >
> >
> > How could I get to communicate Oracle with Excel, about this.
> >
> > Or are there good alternatives for this idea?
> >
> >
> >
> > Like to here from you.
> >
> > Siebe Jongebloed (Senior Consultant Content Management)
> >
> >
>
> I think in your situation time has come to replace the flat tire instead of
> trying to repair it. You are coping with a disaster situation and the only
> *real* solution is to implement the business-rules where they belong: in
> the database. IMO, there can be no discussion about that.
> Any other solution is going to be frought with errors.
> It shouldn't also be that difficult to change them.
> Quite likely your *real* problem is the business-'rules' are changing on the
> fly, by the day, and possibly even per person. No application can automate
> chaos, and usually the chaos even gets worse once it is automated. I've been
> there quite a lot of times: there are too many organisations that don't know
> what they want.
> The evident method to *repair* your flat tire, is to keep track of changed
> records with your database trigger, and have an Excel/VBA application filter
> those, apply the business-rules and have the VBA application update the
> database. Preferably this should be a VB application calling Excel and using
> Oracle Objects for OLE to prevent wastage of resources.
> However, I have to stress the real solution is to implement business-rules
> in the database and maybe to change processes.
> >
>
> Regards
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
> >
> >
> >
> >
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> schreef in bericht
> > news:7o5eiu0pp8t5nepl48pbmeu99kij409700_at_4ax.com...
> > > On Sat, 06 Jul 2002 15:17:41 GMT, "Siebe Jongebloed"
> > > <s.jongebloed_at_chello.nl> wrote:
> > >
> > > >Hi there,
> > > >
> > > >I would like to combine functionality of Oracle and Excel.
> > > >
> > > >The idea is to use Oracle-Table-records-columns inside
> excel-spreadsheet
> > to
> > > >calculate new values of other Oracle-Table-records-columns. The
> > spreadsheet
> > > >itself should also reside inside a Oracle-'BLOB' with information on
> > which
> > > >Oracle-source-records are used inside the excel-spreadsheet.
> > > >
> > > >A trigger inside Oracle should cause
> > > >
> > > > 1.. a particularly spreadsheet be sent to a oracle-client machine,
> > > > 2.. start excel,
> > > > 3.. update target values,
> > > > 4.. as soon as source-records have changed.
> > > > 5..
> > > >How is this possible?
> > > >
> > > >
> > > >
> > > >Thanks in advance.
> > > >
> > > >
> > > >
> > > >Siebe.
> > > >
> > > >
> > > By creating a disaster application, which is the architecture you lay
> > > down above.
> > > PL/SQL can just do any calculation, and I can't imagine that anyone
> > > with only a bit of sanity in his mind would endeavour to write an
> > > external function to call Excel from a trigger, while Excel can call
> > > Oracle using ODBC.
> > > In short: either try to explain what is so complicated that would
> > > require Excel, or try to get acquainted with ODBC and VBA to implement
> > > your requirements directly in Excel.
> > > Calling Excel from a trigger is just not going to work, unless this is
> > > a mickey mouse app, which is supposed to work from one client only.
> > >
> > > Regards
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> >

From the standpoint of data integrity I am absolutely in agreement with Sybrand. What you propose is a train wreck without witnesses.

The truth from my experience is that the only reason anyone wants to take data out of a relational database and put it into a spreasheet is so that they can change the numbers and lie to management, stockholders, or regulators.

I've heard lots of people claim otherwise. But, in the end, it is always the same.

Daniel Morgan Received on Mon Jul 08 2002 - 10:40:27 CDT