Native compilation of PL/SQL in Oracle 10G (Express) for Windows (32-bit) using MinGW compiler.

Usually machine code works faster than any kind of P-Code. It should be applicable to Oracle PL/SQL code as well. By default PL/SQL is stored as P-Code in a database and is executed by interpreter. However, since Oracle 9i Oracle can execute natively compiled code. It means Oracle (9i and 10g) can translate P-Code into C language, after that it can use C compiler and linker to produce DLLs (one DLL per PL/SQL unit). Oracle 11g can compile native code directly without any 3-rd party compiler.

Oracle 9i for Windows required MS Visual C compiler. Oracle 10g supports MinGW GCC as well.

Although Oracle Express 11G is available, in many places people still use 10G Express.

For training exercise let us setup native compilation using MinGW on 10G Express (10.2.0.1).

1. Check current PL/SQL compilation status

a. select * from v$parameter where name like 'plsql_native_library_dir';

b. select name,type,plsql_code_type FROM dba_plsql_object_settings where owner='SYS';

c. select * from ncomp_dll$;

2. Install MinGW (from www.mingw.org)

3. Configure Oracle according to Ref 1.
The free unpatched Oracle XE 10G has some bugs in spnc_commands file and in the documentation. To make it work with MinGW modify $(ORACLE_HOME)/plsql/spnc_commands, comment all default commands and add following two commands (one line per command):

C:/MinGW/bin/gcc -c %(src) -O1 -I$(ORACLE_HOME)/rdbms/public -I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/OCI/include -o %(obj)

C:/MinGW/bin/ld -shared -e 0x0 %(obj) -L. -Lc:/MinGW/lib C:/Windows/System32/crtdll.dll $(ORACLE_HOME)/lib/orapls10.lib -o %(dll)

a. I would also recommend to wrap gcc and ld into scripts that would allow to spool compilation details, errors and results into a log file.

i. Wrapper for gcc. C:\oraclexe\app\oracle\product\10.2.0\server\plsql\bin\gcc_wrapper.cmd

set LOG=C:\oraclexe\oradata\XE\nativelib\compile.log

date /T >>%LOG%

time /T >>%LOG%

echo c:/MinGW/bin/gcc %* >>%LOG% 2>&1

c:/MinGW/bin/gcc %* >>%LOG% 2>&1

set R=%errorlevel%

echo errorlevel=%R% >>%LOG% 2>&1

exit /b %R%

ii. Wrapper for ld. C:\oraclexe\app\oracle\product\10.2.0\server\plsql\bin\ld_wrapper.cmd

set LOG=C:\oraclexe\oradata\XE\nativelib\compile.log

echo c:/MinGW/bin/ld %* >>%LOG% 2>&1

c:/MinGW/bin/ld %* >>%LOG% 2>&1

set R=%errorlevel%

echo errorlevel=%R% >>%LOG% 2>&1

exit /b %R%

iii. In this case modify your spnc_commands following way:

C:/oraclexe/app/oracle/product/10.2.0/server/plsql/bin/gcc_wrapper.cmd -c %(src) -O1 -I$(ORACLE_HOME)/rdbms/public -I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/OCI/include -o %(obj)

C:/oraclexe/app/oracle/product/10.2.0/server/plsql/bin/ld_wrapper.cmd -shared -e 0x0 %(obj) -L. -Lc:/MinGW/lib C:/Windows/System32/crtdll.dll $(ORACLE_HOME)/lib/orapls10.lib -o %(dll)

4. Create directory for DLLs C:\oraclexe\oradata\XE\nativelib

5. Reconfigure Oracle. Run sqlplus / as sysdba

shutdown immediate

startup upgrade

alter system set plsql_native_library_dir= 'C:\oraclexe\oradata\XE\nativelib' scope=both;

alter system set plsql_native_library_subdir_count=0 scope=both;

alter system set plsql_optimize_level=2 scope=both;

alter system set plsql_code_type = NATIVE scope=both;

@?/rdbms/admin/utlrp.sql

@?/rdbms/admin/dbmsupgnv.sql

shutdown immediate

startup

@?/rdbms/admin/utlrp.sql

@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
-- run utlrp until it returns 0 ERRORS DURING RECOMPILATION

6. Check new PL/SQL compilation status

select name,type,plsql_code_type
from dba_plsql_object_settings where owner='SYS';

select * from ncomp_dll$;

References:

Ref 1: Oracle® Database Installation Guide. 10g Release 2 (10.2) for Microsoft Windows (32-Bit). Part Number B14316-04
4.3.16 Configuring GNU Compiler Collection as the Primary Compiler

http://docs.oracle.com/cd/B19306_01/install.102/b14316/postcfg.htm

Ref 2: Oracle® Database PL/SQL User's Guide and Reference. 10g Release 2 (10.2). Part Number B14261-01

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/toc.htm