Advantages of External Routines
External Routines offer the following advantages:
- Reusability, efficiency, and modularity.
- DLLs already written and available in other languages can be called from PL/SQL programs.
- The DLLs are loaded only when needed, so memory is conserved.
- The DLLs can be enhanced without affecting the calling programs.
- External Routines are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes.
- External Routines enable us to move computation-bound programs from client to server, where they will execute faster thanks to more computing power and less across-network communication.
- Interface the database server with external systems and data sources.
- Extend the functionality of the database server itself.
Disadvantages of External Routines
The disadvantages of using the External Routines are:
- The EP feature is only available on platforms that support DLLs or Shared Object libraries.
- Routines are only callable from C code (not C++).
- You cannot pass PL/SQL cursor variables, records, collections, or instances of an object type to an external Routine.
- You cannot use Remote Server/Distributed processing using the CREATE LIBRARY clause.
- The extproc must be started on the same server where the extprocs are being used.
- The maximum number of parameters that you can pass to a C external Routine is 128. Limit on the same depends on the OS you use.
Ajay Gursahani, Mahindra-British Telecom Limited