"Kempf, Reed" wrote:
>
> There is a some good info on working with external procedures out on
> metalink and also on this orafaq document.
>
Just a note about external procedures. I'd rather not use them if many
concurrent users need to call the routine. The reason is that every
session will have a shadow 'runproc' program running, the purpose of
which is to load on demand the routine from a shared library, execute
and return the result - if you have many concurrent sessions the
extra-load may be significant (not too mention that if you want C just
to get better performance than with PL/SQL, you may be disappointed). It
is of course OK if your connections are pooled or if your routines are
used by a single batch program. There are also a number of concerns
which may be a pain when coding :
- Oracle mention that runproc is currently (8i, unchecked with 9i)
unthreaded, but they leave the option open for the future. Watch
therefore critical sections and polish your semaphores.
- Static variables are forbidden, because you do not control when the
function is reloaded in memory (resetting everything). A workaround
might be, if you need some persistence between successive calls, shared
memory, but I still have to play with this.
In brief, uncareful coding may lead to bugs, if not right now, in a near
future, and they may be difficult to find then.
An alternative to consider would be, in my opinion, a dedicated 'service
provider' daemon, which could answer requests through dbms_pipe - the
'give me a quote' example in the Oracle documentation for pipes is
perfectly adequate. The 'service provider' could collect the information
from a Reuters or Bloomberg flow and send it back, a certainly more
efficient solution than external procedures getting the quote directly
from the non-Oracle source. Of course, then you have the problem of
starting/stopping the server, which is a non-problem with runproc.
My 0.02c.
--
Regards,
Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
