Chapter 4. Using Advanced Interface Methods

External routines enable the communication between the Oracle database and the programs, which are written in non-database language such as C, C++, Java, or COBOL. The fact is not new that Oracle has emerged as a compatible database manager for application clients. But from the programming and development perspective, every language has its own special features and utilities. Before UTL_MAIL and UTL_SMTP came to the rescue, there was no option for sending an e-mail in Oracle. In some situations, a special logic written on the client side is required to be used on the server side too. The idea is to resolve the situations where a non-PL/SQL program has to be invoked from PL/SQL. To confront such situations, Oracle introduced external routines in its eighth release (Oracle 8) to bridge the gap between the database and non-PL/SQL programs. This chapter covers the external procedures and its implementation in the following topics:

• Understanding external...

Understanding external routines

An external routine allows a program, which is written in a language other than PL/SQL, to be used in PL/SQL. For instance, a program logic written in Java can be invoked and used from PL/SQL. The program in non-PL/SQL language is referred to as external programs. The favorable situations for the use of external routines may arise in an application development environment which follows strict regimentation of the client, API and database layers. On broader terms, an external program has to be stored as a shared library on the server followed by its publishing through call specification. Once the external routine is published, it is ready to be used in PL/SQL.

Within the scope of this chapter, we will discuss the invocation of external programs written in C and Java language from PL/SQL.

Architecture of external routines

Before we get into the core methodology followed for external routines, we will discuss some of the crucial components of Oracle architectural...

Executing external C programs from PL/SQL

Oracle extends the architectural support to the external programs in C, C++, Java, or the one whose library is interpretable by C. In the external procedure architecture, we saw the processing steps of an external program. Now, we will list the development steps to run a C program from PL/SQL:

1. Write a C program and compile it.

2. Copy the C program's code file in the $ORACLE_HOME\bin\ directory. Generate the DLL using a hardware supported native C compiler.

   Tip

   For demonstration purposes, we will use the MingW (Minimal GNU for Windows) compiler to compile C program and generate the DLLs.

   It can be downloaded from http://www.mingw.org/wiki/Getting_Started.

3. Configure the Oracle Net service.

4. Create a PL/SQL library object for the DLL.

5. Create a call specification in PL/SQL to publish the external program. Specify the external language used in the program (here, it is C), the PL/SQL library name, the method in the external program and the parameters mapped as per...

Executing Java programs from PL/SQL

Similar to C programs, Oracle can communicate with Java classes as external procedures, too. Unlike C external programs, the Java classes and Java source files are stored as schema objects in the database. But the caveat here is that the Java classes must be operational in the logical side and not on the user interface. It implies that the Java programs which offer generic utility or data processing operations are the best candidates to be loaded into Oracle.

Invoking Java code from PL/SQL not only reduces the network overhead from the client but it also helps in the distribution of logic across the layers and reduces code redundancy. Under this section, we will discuss how to load a Java class into the database and access it through the call specification.

Unlike C external procedures, calling Java program from PL/SQL is convenient. There is no dependency on the shared library, too. But yes, there is something known as Libunit which is like the Java shared...

Summary

This chapter has covered the philosophy, architecture, and support for external routines in PL/SQL. We learned the external routine concepts from basic to advanced level. We demonstrated the execution of C and Java programs as external procedures.

In the next chapter, we will learn a fresh application data security feature known as Virtual Private Database

Practice exercise

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

2. Oracle 7 introduced the external procedure feature for sending mails from PL/SQL.

   1. True

   2. False

3. Determine the effect of dropping the library object which has been used in a PL/SQL call specification and 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 been already executed once

   4. The PL/SQL wrapper method gives no output

4. 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.ora
   SID_LIST_LISTENER...