Advantages of External Routines

External Routines offer the following advantages:

  1. Reusability, efficiency, and modularity.

  2. DLLs already written and available in other languages can be called from PL/SQL programs.

  3. The DLLs are loaded only when needed, so memory is conserved.

  4. The DLLs can be enhanced without affecting the calling programs.

  5. External Routines are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes.

  6. 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.

  7. Interface the database server with external systems and data sources.

  8. Extend the functionality of the database server itself.

Disadvantages of External Routines

The disadvantages of using the External Routines are:

  1. The EP feature is only available on platforms that support DLLs or Shared Object libraries.

  2. Routines are only callable from C code (not C++).

  3. You cannot pass PL/SQL cursor variables, records, collections, or instances of an object type to an external Routine.

  4. You cannot use Remote Server/Distributed processing using the CREATE LIBRARY clause.

  5. The extproc must be started on the same server where the extprocs are being used.

  6. 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