Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Start MS-excel from oracle on clientPC

Re: Start MS-excel from oracle on clientPC

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 7 Jul 2002 12:30:45 +0200
Message-ID: <uig6u3c1utmc5e@corp.supernews.com>


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
>
>
Received on Sun Jul 07 2002 - 05:30:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US