Compiling PL/SQL Code for an Oracle Database

Perform One-Time DBA Setup

Native PL/SQL compilation is achieved by translating the PL/SQL source code into C source code that is then compiled on the same host machine running the Oracle server. The compiling and linking of the generated C source code is done by tools external to Oracle that are set up by the DBA and/or system administrator.

Enabling native PL/SQL compilation in Oracle Database 10g can be accomplished in as few as three steps:

1. Get a supported C compiler.
   
2. Set up directory(ies) in the filesystem that will hold the natively compiled files.
   
3. Check $ORACLE_HOME/plsql/spnc_commands.

Step 1: Get a Supported C Compiler

If you don’t already have your platform vendor’s usual C compiler, you’ll have to get one from somewhere. Fortunately, this does not always require a huge investment; if you happen to be running Oracle Database 10g Release 2, you can use the freely downloadable GNU C compiler. Table 20-1 shows just a few of the combinations of compiler, version, and platform that Oracle supports. For the complete list, go to Oracle’s Metalink site and search for the “Certified Compilers” document (doc ID 43208.1).

a Obtained by installing MinGW-3.1.0-1.exe from http://www.mingw.org

With the right combination of luck and spare time, you may be able to get an unsupported compiler to work for native compilation; if you have trouble, though, all Oracle will do is tell you to get a certified compiler. I know that some sites have been able to use GCC on Sun Sparc Solaris with Oracle9i Database, but others had trouble until they got Sun’s compiler. And I have never heard of anyone getting GCC working with Oracle Database 10g Release 1.

By the way, you cannot reuse the generated object files on another machine, even if it’s the exact same version of the OS and Oracle; you can’t even copy the object files to a different database on the same machine. The object files contain database-specific information and must be generated on the exact same database and machine that will ultimately run the files. Besides, you might have a DDL event that triggers some automatic recompiles. You will, therefore, need a C compiler on every machine on which you want to use this feature. And, if you happen to be running an Oracle Real Application Cluster (RAC), you’ll need to install your C compiler on each node.

Step 2: Set Up the Directories

When Oracle translates your PL/SQL into C and runs it through the host compiler, the resulting object files have to go somewhere on the server filesystem. Curiously, there is no default for this location; the DBA must create the directories and set one or two initialization parameters. Here is a simple case:

  # While logged in as oracle (to get the correct ownership):
  $ mkdir
/u01/app/oracle/oracle/product/10.2.0/ db_1/dbs/ncomps

  $ sqlplus "/ as sysdba"
  ...
  SQL> ALTER SYSTEM SET plsql_native_library_dir =
    2  '/u01/app/oracle/oracle/product/ 10.2.0/db_1/dbs/ncomps';

Some filesystems start to choke on a few thousand files in a single directory; to support that many modules, you can get Oracle to spread the object files across many subdirectories. To use 1,000 subdirectories, specify:

  SQL> ALTER SYSTEM SET plsql_native_library_subdir_count = 1000;

You will also need to precreate the subdirectories, which in this case must be named d0, d1, d2...d999. Do this to generate a directory-creating script (using a variation on Oracle’s suggested method):

  SET DEFINE OFF
  SPOOL makedirs.sh
  BEGIN
    FOR dirno IN 0..999
    LOOP
      DBMS_OUTPUT.PUT_LINE('mkdir d' || dirno || ' && echo ' || dirno);
    END LOOP;
  END;
  /
  SPOOL OFF

Then, edit out the cruft at the top and bottom of the script, and at the operating system prompt, do something like this:

  $ cd /u01/app/oracle/oracle/product/ 10.2.0/db_1/dbs/ncomps
  $ sh makedirs.sh

Starting with Oracle Database 10g Release 1, the master copy of the object files is really BLOB data in a table named ncomp_dll$; the on-disk copy exists so it can be dynamically loaded by the operating system. With this capability, Oracle can regenerate the on-disk copies without recompiling the source, but you still don’t want to delete any of the generated files unless your database is shut down.

Step 3: Check $ORACLE_HOME/plsql/spnc_commands

Oracle Database 10g invokes the C compiler by calling a script named spnc_commands (spnc stands for “stored procedure native compilation,” presumably). This file differs by platform, and in some cases includes inline comments indicating how to use different compilers. You’ll want to inspect this file to see if the path to the compiler executable is correct for your installation.

If you’re running Oracle9i Database, there is a file named spnc_makefile.mk that you will need to inspect instead; that version has a more complicated setup for native compilation (see the sidebar “Native Compilation Prior to Oracle Database 10g”).

Native Compilation Prior to Oracle Database 10g

With native compilation in Oracle9i Database, the setup is slightly different, and there are several limitations that you’ll want to keep in mind:

1. In addition to getting a supported C compiler, setting the directory parameters, and inspecting the spnc_makefile.mk as discussed above, there are extra parameters required at the system or session level: PLSQL_NATIVE_MAKE_UTILITY (typically make) and PLSQL_NATIVE_MAKE_FILE_NAME (the fully qualified path to spnc_makefile.mk).
   
2. While a CREATE or REPLACE of a module replaces the existing DLL, a DROP of a module does not delete the DLL from the filesystem. You will need to remove these files manually.
   
3. There is no storage of the content of the object file in the data dictionary as in Oracle Database 10g. You definitely want to adjust your system-level backup to include the native file directories.
   
4. Package specifications and package bodies must match in compilation style (both interpreted, or both native). In Oracle Database 10g, they don’t have to match.

Native compilation does take longer than interpreted mode compilation; our tests have shown an increase of a factor of about two. That’s because native compilation involves several extra steps: generating C code from the initial output of the PL/SQL compilation, writing this to the filesystem, invoking and running the C compiler, and linking the resulting object code into Oracle.