External Procedures and their uses in Oracle10g

Oracle has provided lots of features that make external procedures beyond doubt an industrial-strength source. The strengths and capabilities of different languages are available to users. In situations where a particular language does not provide the features required code written in some other language can be reused by external procedures.

Oracle Database lets the user work in different languages such as PL/SQL, C by means of Oracle Call Interface, C++ or C by means of Pro*C++/C precompiler, COBOL by means of Pro*Cobol precompiler, Visual Basic by means of Oracle objects for OLE and Java by means of JDBC API. The choice of these languages depends on how the application needs to work with Oracle Database. With PL/SQL External Procedures the user can write C function calls as PL/SQL bodies.

The database has a special purpose interface, the call specification, that lets the user call external procedures from other languages. While this service is intended for inter communication between SQL, PL/SQL, C and Java, it is also available from any base language that can call these languages. An external procedure or external routine is a procedure stored in a dynamic link library (DLL) or libunit. This procedure is registered with the base language and then called.

External procedures have several advantageous features; some of them are as follows. It can isolate execution of client applications from the database instance to prevent problems in client side from affecting the database. It can help to provide the interface between database server with external systems and data sources. Oracle external procedures use shared libraries rather than execuatables and allow user’s to consolidate different routines together into a single shared library file for convenience and increased performance. Oracle enforces the execution of external procedures with database level security.

External procedures are published through call specifications, which make available a superset of the AS EXTERNAL function through the AS LANGUAGE clause. In general call specifications enables Dispatching the appropriate C or Java target procedure, Datatype conversions, Parameter mode mappings, Automatic allocation of memory and cleanup, Purity constraints to be specified where necessary for packaged functions called from SQL, Calling Java methods or C procedures from Database triggers etc. The external procedures are made available to PL/SQL by a process called Loading. Oracle Database can only make use of the external procedures that are published through the call specification that maps names, parameter types and return types for the Java class method or the C external procedure to their SQL counterparts.

Call specifications can be located in standalone PL/SQL Procedures and Functions, PL/SQL Package specifications, PL/SQL Package bodies, object type specifications and object type bodies.

When the external procedure is published, it can be invoked. Generally the CALL statement is used to call the PL/SQL sub program that published the external procedure. These CALL statements can appear in anonymous blocks, standalone and packaged programs, methods of an object type, Database Triggers or SQL statements (calls to packaged function only).

Any PL/SQL blocks or sub program unit executing on the server side or on the client side can make a call to an external procedure. Before external procedures are called, the privileges and permissions that exist in the executing environment should be considered. The external procedures call through CALL specifications execute with definer’s privileges and not invoker’s privileges. A synonym can be created for an external procedure for the sake of convenience. When PL/SQL calls an external C procedure it finds the path of the DLL from the Data Dictionary. Then it alerts a Listener process which in turn spawns a session specific agent. This is by default named the extproc. The listener hands over the connection to the agent, PL/SQL passes the name of the DLL, the name of the external procedure and parameters if any to the agent.

This agent loads the DLL and runs the external procedure. The agent also handles service calls to Oracle Database and passes to PL/SQL the return values. Even if the external procedure completes the agent remains active throughout the session.

There are service routines that can be called from an external procedure. For example the OCIExtProcAllocCallMemory() allocates n bytes of memory for the duration of the external procedure call. The OCIExtProcRaiseExcp can help raise a predefined exception with a valid Oracle database error number in the range 1..32767. The OCIExtProcRaiseExcpWithMsg is a service routine that helps raise a user defined exception and return a user defined message.

Usually external procedure fails when the prototype specified does not match with the one generated internally by PL/SQL. To help debug external procedures PL/SQL has the utility package DEBUG_EXTPROC. The Oracle Database account must have EXECUTE privileges on the package and CREATE LIBRARY privileges to use the package.

Though external procedures have a wide range of use, there are certain restrictions some of them given as follow. The feature is offered only on platforms that support DLL. Only C procedures and procedures callable from C code are supported. PL/SQL cursor variables or records cannot be passed to an external procedure. The number of parameters that can be passed to an external procedure is limited.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |