Subject:  Overview of How an External Procedure Works 
Creation Date:  01-JUN-2000 


PURPOSE
-------

To provide basic information about external procedures.

 
SCOPE & APPLICATION
-------------------

Basic information for individuals with an intermediate
understanding of Oracle architecture.


Overview of How an External Procedure Works
============================================

An external procedure or routine lets you:

* Move computation-bound programs from client to server where they execute
  faster (because they avoid the roundtrips entailed in across-network
  communication) 

* Interface the database server with external systems and data sources 

* Extend the functionality of the database server itself 


The following describes how an external procedure is typically created
during useage of InterMedia Text, though much of it applies to all
external procedures.


1. A client program connects to the database either locally or through
   the Listener.

2. The listener's environment is used to spawn an Oracle shadow process 
   unless the 'ENVS=' statement is in the 'SID_DESC' section of the 
   listener.ora.  Settings in the'ENVS='will take precedence over
   the existing environment.

3. Once the shadow process is started the client and shadow process talk
   directly. The listener's job is done for now.

5. The client program issues a standard SQL statement to create a iMT
   index.  This initiates the external procedure creation process.

6. The 'EXTPROC_CONNECTION_DATA' service information in the tnsnames.ora
   of the server's environment is provided to the Listener, effectively
   saying 'This is what I need to talk to a create an iMT index'.
   The environment from the Listener will be used for spawning subsequent
   Oracle processes or external procedures unless the 'ENVS=' statement
   exists under the 'SID_DESC' section of the listener.ora.  Settings in
   the'ENVS='will take precedence over the existing environment.

7. The Listener resolves that service name to a specific SID in the 
   listener.ora, then launches a extproc process (indicated under the
   'SID_NAME' section') to allow direct communication with the database.
   The spawned process will have a name that equals the 'SID_NAME=' 
   section concatenated to the 'PROGRAM=' section. 


Spawning an External Procedure after connecting through the Listener.

_______________         ____________ _____________                   _
 |             |  1.   |            |             |                 | 
 |tnsnames.ora |-----> |listener.ora| environment |---------------->| 
 |_____________|       |   -----    |_____________|                 |S
C|                     |   -----    |  2. A server process is       |E
L| 3.'You can talk now'|  Listener  |     spawned from the          |R
I|<--------------------|____________|     listener's environment.   |V
E|                                                                  |E
N| 4. Communication is now possible.                                |R
T|<---------------------------------------------------------------->| 
 |                                                                  |P
P| 5. A statement is issued that requires an external procedure.    |R
R| ---------------------------------------------------------------->|O
O|                       ____________                  _____________|C
C|                      |            |                |             |E
E|                      |listener.ora| <--------------|tnsnames.ora |S
S|                      |   -----    |  6. Service    |_____________|S
S|                      |   -----    |     Request                  |
 |                      |  Listener  |                              | 
 |                      |____________|                              |
 |                           | 7. Resolves the service request      |
 |                           |    & spawns the extproc process.     |
 |                            -----------> ____________________     |
 |                                        |                    |    | 
 |                                        | External Procedure |    |
 |<-------------------------------------->|      Process       |<-->|
_|                                        |____________________|    |_


RELATED DOCUMENTS
-----------------
Oracle8i Application Developer's Guide - Fundamentals