Chapter 5. Using Advanced Interface Methods

Oracle enables the application developers to create PL/SQL routines that can invoke programs written in non-Oracle programming languages. An external program can be a C, C++ based, or Java-based program. External procedures were introduced in Oracle 8i to add extensibility to the Oracle Database engine by allowing the non-Oracle programs to execute in the database kernel. This chapter covers the external procedures and their implementation in the following topics:

• Understanding external routines

• Architecture and benefits

• Executing external C programs from PL/SQL

• Executing external Java programs from PL/SQL

Overview of External Procedures

Oracle provides a rich platform for application programming through PL/SQL and the support for multilanguage programs. Programming languages that allow the technical translation of real world problems have different features and advantages, for example, PL/SQL is tightly integrated with SQL and computation-based logic can be run efficiently in C. Some of the crucial factors that determine the choice of a programming language can be code performance, portability, and security. Therefore, you might see application development involving multiple programming languages.

Oracle PL/SQL allows users to create a PL/SQL program that calls a C or Java procedure. The special purpose interface allows the users to utilize the strengths and features of multiple programming languages and support code reusability.

External Procedures

An external procedure is stored in a dynamic linked library (DLL) or a similar unit, which is prototyped in a call specification using Oracle PL...

Components of external procedure execution flow

In this section, we will learn about the database components involved in the execution of an external procedure.

The extproc agent

The extproc agent plays the most vital role in interfacing external procedures in the Oracle Database. It is a process started by the Oracle Database or database listener, which facilitates the execution of an external procedure whenever required. It carries relevant information such as the library path, procedure name, and arguments. It returns the result of the execution back to the database processing engine.

In a dedicated server mode, each session will have a new instance of the extproc process. In a multiuser application, multiple sessions with multiple extproc processes may result in a disproportionate allocation of memory resources. In a shared server mode, a multithreaded extproc agent can provide the efficient utilization of server resources by maintaining a shared pool of the extproc processes.

The Library...

Executing external C programs from PL/SQL

Let's us walk through an illustration on how to execute an external procedure, written in the C language, in the Oracle Database.

• Step 1: Creating and compiling the C program.

  The following C program (GetMax.c) finds the maximum of the two number values:

  #include <stdio.h>
  
  /* Define the function returning the max between two numbers */
  int GetMax(int num1, int num2)
  {
     /* local variable declaration */
     int result;
  
     if (num1 > num2)
        result = num1;
     else
        result = num2;
  
     return result;
  }

  Compile the program by using a C compiler.

  sh-4.3# gcc -c GetMax.c

• Step 2: Generating the DLL and creating the library object in the Oracle Database.

  We will now generate the DLL for the C program:

  sh-4.3# gcc -shared GetMax.c -o GetMax.dll

  • The library location

    The default paths searched by the extproc process for loading the required library are $ORACLE_HOME/bin and $ORACLE_HOME/lib. For testing purposes, we will continue with the defaults....

Securing External Procedures with Oracle Database 12c

The Oracle Database creates the extproc process and runs under the operating system user, that starts the listener or runs an Oracle server process. Quite often, you will see the extproc process running as the oracle user. The extproc process is not physically associated with the Oracle Database.

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and associates it with the PL/SQL library object. Whenever the application calls an external procedure, the extproc process authenticates the connection before loading the shared library.

The DBMS_CREDENTIAL package allows the configuration of the credential through member subprograms. The CREATE LIBRARY statement has been enhanced for credential specification. A new environment variable, ENFORCE_CREDENTIAL, can be specified in extproc.ora to control the authentication by the...

Executing Java programs from PL/SQL

Like the C programs, Java programs can also natively execute in the Oracle Database. However, unlike C external programs, the Java classes and Java source files are stored as schema objects in the database.

External Java programs are not executed through OS-based shared libraries but use a Java shared library or libunit for execution. Libunits are similar to dynamically linked libraries but they are mapped one-to-one to the Java class and are not sharable across the other methods.

The libunit is loaded and executed by the Java Virtual Machine (JVM) which resides natively in the Oracle Database. The JVM uses the Java pool component of the shared global area to execute the Java-based external program.

Loading a Java class into a database

A Java program can be loaded into an Oracle Database by using the CREATE JAVA statement or the LOADJAVA utility.

The CREATE JAVA statement enables the Java Virtual Machine library manager to load a Java class into the Oracle...

Summary

External procedures demonstrate the extensibility feature of an Oracle Database that allows a program developed in non-Oracle scripting language to be executed natively in the database kernel. The readers will find it interesting to explore how they can enable complex algorithms to run on the server side.

In the next chapter, we will be discussing a data security feature called Virtual Private Database. From a data access standpoint, this feature is quite important and easy to implement with bigger benefits.

Practice exercise

• Which of the following statements are true about the extproc process?

  1. It loads the shared library of the external C program.

  2. It is started by the PL/SQL runtime engine.

  3. It is a session-specific process.

  4. The extproc process compiles the C program while loading.

• Oracle 7 introduced the external procedure feature for sending e-mails from PL/SQL.

  1. True

  2. False

• Determine the effect of dropping the library object that has been used in a PL/SQL call specification while it is still in use:

  1. The PL/SQL wrapper method gets invalidated.

  2. The shared library gets corrupted.

  3. The PL/SQL wrapper method still works fine as it has already been executed once.

  4. The PL/SQL wrapper method gives no output.

• Examine the following TNSNAMES.ora and LISTENER.ora entries and choose the correct option:

  //TNSNAMES.ora
  ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
        (SID = extproc)
        (PRESENTATION = RO)
      )
    )
  //LISTENER...