DPSP
Contacts About Us


Powered by Dynamic PSP
  1. What is Dynamic PSP?
  2. What are the differences between Oracle PSP and Dynamic PSP?
  3. Why not use JSP?
  4. Why Oracle still supports PL/SQL when they integrated Java into Oracle RDBMS?
  5. How To Secure Dynamic PSP Development Interface?
  6. How to enable native PL/SQL compilation in 9i Release 2 on Windows?
  7. I am unable to login into Unit Commander using default account.
  8. What is WebDAV?
  9. How to avoid redirection from index.html to DPSP unit?
  10. 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
  11. 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 (9.2.0.1):

  1. Create ORAPLS9.LIB from ORAPLS9.DLL.

    As of 9i Release 2 patch set 1 (9.2.0.2) and later, this step is no longer necessary, as ORAPLS9.LIB is supplied with this patchset. Those readers who have 9.2.0.2 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 %ORACLE_HOME%\lib directory.
    You can download ready-made library here: orapls9lib.zip (note that this file is valid only for initial release of 9i R2 - version 9.2.0.1. 9.2.0.2 and later patch sets provide their own library file.)

  2. Replace the makefile provided by Oracle with the one that works.

    Yes, spnc_makefile.mk in %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 file locations.

  3. Install Cygwin rm and mv utilities.

    Makefile I provided uses rm and mv commands instead of conventional DEL and 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 rm.exe, mv.exe and required Cygwin library DLL here: fileutils.zip (included is cygwin1.dll version 1.3.22-1 and rm.exe and mv.exe from GNU fileutils 4.1.1), and copy the files from this ZIP to your %SystemRoot%\system32 folder.

  4. Ensure your PATH environment contains paths to necessary VC++ dirs.

    This is necessary so that cl and 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 PATH variable). 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

    %MSDevDir%\BIN;%MSVCDir%\BIN;%VSCommonDir%\TOOLS\%VcOsDir%;%VSCommonDir%\TOOLS;

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

    C:\PROGRA~1\MICROS~1\Common\MSDev98\BIN;C:\PROGRA~1\MICROS~1\VC98\BIN;
    C:\PROGRA~1\MICROS~1\Common\TOOLS\WINNT;C:\PROGRA~1\MICROS~1\Common\TOOLS;

    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 under LocalSystem account and this account only reads environment on OS startup).

  5. 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
as
   dt DATE;
begin
  dbms_output.put_line('Hello world');
  select sysdate into dt from sys.dual;
  dbms_output.put_line('Today is '||to_char(dt,'DD Mon, YYYY HH24:MI:SS'));
end;
/

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 runs as 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 Write, Modify and 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.

Happy ncomping!

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.

Back to the top of page

email us
Copyright © 2000-2014 by HitMedia LLC. All Rights Reserved.