Re: Start a program from wthin Oracle

"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message news:<g56Q9.1219$LA1.142604550_at_newssvr11.news.prodigy.com>...
> As for my situation, I considered the UTL_FILE and cron (or Windows schedule
> job) but I dislike the idea. When the application is NOT running the cron
> job is still running checking to see if the file written UTL_FILE by exists.
> On the other hand, to make the application responsive to the end-user you
> need to have the cron job running and running often. This wastes resources
> and/or causes end-user satisfaction problems.

I am sure your server should be able to handle a cron job running often without crashing... i.e. unless you are running a 20 year old machine... and I am not being sarcastic here!!

>
> My approach is not to use JAVA/JNI but to use an external procedure written
> in C/some language to either perform the requested function or call a script
> to perform the function. The procedure also needs to use reentrant code
> (because of multiple users executing the application at the same time).
> This solves the above two problems because the external program executes
> when necessary (and only when necessary).
>

Bert,

The bigger problem, IMO, is not whether someone is using Java or C external procedure calls (Although I believe it does play a part when database is migrated between different platforms), but it is the process of creating another session in the database by a call from an existing session. e.g. Here is a usecase,

There are 100 users logged in. 50 users out of them click on a button at the same time to run your procedure which runs a batch script that connects to the database to extract some data. Doing that, all you have done is create 50 extra sessions in the database utilizing resources. I think one or two cron jobs will be far less resource hungary then 50 extra sessions in the database.

Unless otherwise it is impossible to accomplish, an existing session in the database should not be in the business to go out of the database only to create few more sessions. IMHO, this kind of stuff should be resolved using independent processes...which means one process to write data to file then RETURN and another to grab data from that file and do whatever with it. Creating extra sessions from within the database is a bad design which will result in extra headaches for your DBA.

I would welcome any disagreements with solid comments.

Regards
/Rauf Sarwar Received on Tue Dec 31 2002 - 15:32:43 CST