- What is Dynamic PSP?
- What are the differences between Oracle PSP and Dynamic PSP?
- Why not use JSP?
- Why Oracle still supports PL/SQL when they integrated Java into Oracle RDBMS?
- How To Secure Dynamic PSP Development Interface?
- How to enable native PL/SQL compilation in 9i Release 2 on Windows?
- I am unable to login into Unit Commander using default account.
- What is WebDAV?
- How to avoid redirection from index.html to DPSP unit?
- Java class to convert xls to csv or from MS Excel to CSV file into PL/SQL and upload data from .xls file to Oracle
- Java class to convert from DBF (DB2) to CSV file within Oracle PL/SQL
How to enable native PL/SQL compilation in 9i Release 2 on Windows?
By Vladimir M. Zakharychev <bob at dpsp-yes dot com>
Native PL/SQL compilation (this link leads to OTN site, which needs registration to access - registration is free) is a new feature of Oracle9i Database. As such, it was not implemented in
9i Release 1 on Windows (this fact was accounted as a bug in 9iR1). Release 2 was said to support
ncomping the PL/SQL on Windows, but in reality there's a lot of things you need to do before you can
compile your PL/SQL natively. Here are the steps necessary to make it work on Release 2 (126.96.36.199):
As of 9i Release 2 patch set 1 (188.8.131.52) and later, this step is no longer necessary, as ORAPLS9.LIB is supplied with this patchset. Those readers who have 184.108.40.206 or later patch set applied
may skip this step.
Amazingly, Oracle didn't supply this library effectively preventing the code from being linked successfully.
However, you can create this library yourself. Two tools are needed for this: Borland IMPDEF (available
with their free C++ Command Line Compiler) and Microsoft LIB (available as part of Visual C++).
First, you create a .DEF file using
IMPDEF orapls9.def orapls9.dll and then you create a library from it using
LIB /def:orapls9.def /out:orapls9.lib.
When done, copy the library file into your
You can download ready-made library here: orapls9lib.zip (note that this file is valid only for initial release of 9i R2 - version 220.127.116.11. 18.104.22.168 and later patch sets provide their own library file.)
Replace the makefile provided by Oracle with the one that works.
%ORACLE_HOME%\plsql is tailored for UNIX and GNU make and does not work on Windows with Microsoft NMake as it is.
There are a lot of modifications required to this file. I went through the burden of modifying and debugging it and you can download the resulting (verified working) makefile
here: Makefile (right-click on this link and choose Save Target As...)
Be sure to read through comments in this file for additional modifications necessary to tailor this makefile for your particular system and
Makefile I provided uses
mv commands instead of conventional
MOVE Windows commands, and for a reason:
Windows embedded commands do not like UNIX-style filenames (like c:/oracle/ora92/plsql/...) - they expect backslash as separator character and treat slash character as an option specifier,
and subsequently fail to perform the command. Cygwin team ported most UNIX utilities to Windows and we can
take advantage of that. You can download the latest Cygwin library and utilities from their site at http://www.cygwin.com
or you can download only
mv.exe and required Cygwin library DLL here: fileutils.zip (included
cygwin1.dll version 1.3.22-1 and
mv.exe from GNU fileutils 4.1.1), and copy the files from this ZIP to your
PATH environment contains paths to necessary VC++ dirs.
This is necessary so that
link can find needed DLLs. Basically you need to locate
vcvars32.bat file in
your Visual C++
BIN directory and cut
PATH set command from it (actually, you don't need the whole command, you only need paths that this command file appends to the
Then right-click on My Computer, Properties, Advanced tab, Environment Variables..., select System Variables and locate
PATH variable there. If it's not there, create it
clicking on the New button, otherwise double-click and edit it
to include the paths you extracted from
vcvars32.bat (and be sure to expand all variables manually). Unedited, extracted string should look like
and you should edit it and expand variables in it like this (paths and names are for VC++ 6.0, line is wrapped for convenience of the reader but it should actually be contiguous):
using definitions in
vcvars32.bat for these variables. Now just prepend or append the expanded string to the current
PATH value and save it.
You may need to reboot so that OS will re-read the environment and then provide it to Oracle and make utility that Oracle will spawn (remember that Oracle usually starts
LocalSystem account and this account only reads environment on OS startup).
Ok, we're almost there - just need to configure Oracle to know where our files are.
Several initialization parameters need to be set for Oracle to be able to compile PL/SQL natively. Assuming that
C:\VCHOME is Visual C++ home directory,
C:\ORACLE\ORA92 is Oracle home and you
created a directory
C:\ORACLE\ORA92\plsql\nlib to store your DLLs, here's which and how
(you need to issue these commands AS SYSDBA):
ALTER SYSTEM SET plsql_native_make_utility='C:\VCHOME\bin\nmake.exe' SCOPE=BOTH;
ALTER SYSTEM SET plsql_native_make_file_name='C:\ORACLE\ORA92\plsql\Makefile' SCOPE=BOTH;
ALTER SYSTEM SET plsql_native_library_dir='C:\ORACLE\ORA92\plsql\nlib' SCOPE=BOTH;
Be sure not to use
%ORACLE_HOME% in any of these - use full real path this environment variable translates into. Same's with
%VCHomeDir%. Although these variables
could save you a few keystrokes, Oracle does not recognize them as variables and does not expand them into real paths.
That's it. With the last step you defined make utility, makefile and path to receive compiled files and Oracle is now ready to ncomp your PL/SQL. To test if everything is in order, run the test from Oracle documentation
that demonstrates PL/SQL ncomping feature (slightly modified to remove bugs in the source code from documentation):
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
select sysdate into dt from sys.dual;
dbms_output.put_line('Today is '||to_char(dt,'DD Mon, YYYY HH24:MI:SS'));
If you get any errors here, use SHOW ERROR command in SQL*Plus and fix the problem if you can figure it out - regretably, ncomping cuts the make output and it is not always visible what went wrong (but of course,
you are guaranteed to see the Microsoft NMake banner in its entirety. ;) To avoid seeing the NMake banner, you can add /NOLOGO switch to the NMake call you defined in plsql_native_make_utility:
ALTER SYSTEM SET plsql_native_make_utility='C:\VCHOME\bin\nmake.exe /NOLOGO';
Final note: make sure you protected the Makefile,
nmake.exe and other executables it will call during the course of compiling your DLLs from being overwritten by a malicious user. Since Oracle
LocalSystem, all processes it spawns inherit the same privileges and thus are not limited in almost any way on what they can do to OS and files (
LocalSystem is a very powerful account), which makes them
an attractive target for hacker attacks. Ensure that nobody but Administrators can overwrite these files by explicitly revoking
Full Control permissions from all non-admins (especially from
Everyone group) and granting these directly to
Administrators group only. This way, only Administrators will be able to update the executables if need be and nobody else, including
LocalSystem, will be able
to trojan them while still be able to execute them.
P.S. Recently I was asked if it is possible to setup Oracle on Windows to use GCC compiler for PL/SQL compilation. The answer is: definite yes. I've put together a Makefile that uses GCC 3.3 with MinGW for compiling PL/SQL
natively and successfully tested it with GCC 3.3.3 (cygwin special) with MinGW libraries. You can download the makefile here: Makefile.mingw. Read through the comments in this file, make necessary modifications and you should
be able to compile your PL/SQL with GCC on Windows.