Extproc Architecture

One of the efforts in the application development has been to isolate the information source mainly databases from programming logic. External routines are the natural solution for that. As the leading database in the world Oracle also provides integration with external routines. External procedures are the Oracle’s solution for incorporating the programming modules with your database. Oracle provides a comprehensible architecture for that namely as Extproc architecture. Extproc architecture comes with every database that you install.
The default listener for your database does have a handler for Extproc architecture but you need to setup a separate listener for Extproc agent. Oracle’s Extproc allows you to communicate with any language that is callable from C language. However callback to the database is restricted to those languages only that are supported by OCI (Oracle Call Interface). OCI supports C, C++, COBOL, FORTRON, PL/2, Visual Basic and Java. Now let us put it simple what one had to do to run an external routine from Oracle PL/SQL program.
First the language you are using for your external routines should support shared libraries or DLLs. The shared libraries are program modules that you incorporate in your programs. Also the platform you are using also support shared libraries. You need also to configure a separate listener for Extproc agent. And also you should have PL/SQL shared library that will work as a wrapper and will call the external routine. Here is how it works internally.
The PL/SQL wrapper first translates the data types with the data types of your programming language. Secondly it forks a call (RPC) to the external routine. This call is caught by the Oracle Net Services provided the listener is correctly configured. Oracle Net Services then forks an Extproc agent that will communicate with External Shared library. The result of the Shared library is returned to the PL/SQL wrapper. The PL/SQL wrapper translates the data types again to the native data types and displays the results.