Home arrow Oracle arrow Compiling PL/SQL Code for an Oracle Database

Compiling PL/SQL Code for an Oracle Database

In this third part of a nine-part series on managing PL/SQL code, you'll learn how to compile PL/SQL source code, how to use the optimizing compiler, and more. It is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Compiling PL/SQL Code for an Oracle Database
  2. Interpreted Versus Native Compilation Mode
  3. Using the Optimizing Compiler and Compile-Time Warnings
  4. How to turn on compile-time warnings
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 01, 2007

print this article



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

Table 20-1. Sampling of C compilers required by native compilation

  Oracle Database
Platform version(s) Supported C compiler(s)
Sun Sparc Solaris 9.2 Sun Forte Workshop 6.2 (with particular patches from Sun); spnc_command.mk includes gcc-specific comments, but GCC doesnít appear to be officially supported
 10.1 Sun ONE Studio 8, C/C++ 5.5
 10.2 Same as above, plus GCC 3.4
Microsoft Windows 9.2 Microsoft Visual C++ 6.0
2000, XP, 2003 10.1 Microsoft Visual C++ 6.0;  Microsoft Visual C++ .NET 2002;  Microsoft Visual C++ .NET 2003
 10.2 Same as above, plus MinGW GCC 3.2.3a
Linux Intel 32bit 9.2 GNU GCC 2.95.3
 10.1 Red Hat Linux 2.1: GNU GCC
 10.1 Red Hat Linux 3: GNU GCC 3.2.3-2
 10.1UnitedLinux 1.0: GNU GCC 3.2.2-38
 10.1Vendor-independent: Intel C++
 10.2 Red Hat: GCC 3.2.3-34
 10.2Suse: GCC 3.3.3-43
 10.2Vendor-independent: Intel C++ Compiler v7.1.0.28

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):

  SPOOL makedirs.sh
FOR dirno IN 0..999
DBMS_OUTPUT.PUT_LINE('mkdir d' || dirno || ' && echo ' || dirno);

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.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: