How to Compile Oracle PL/SQL using Native Operating Systems (OS) Compiler

1. Either alter system, or in init.ora:

$ mkdir $ORACLE_HOME/plsql/user_shared_libs

SQL> alter system set plsql_native_library_dir=
‘/opt/oracle/product/oracle10g/plsql/user_shared_libs’;

Use the two supplied sample procedures: plsql_inter.sql & plsql_native.sql
or write your own.

2. First compile in normal (INTERPRETED) mode:

SQL> @plsql_inter

Procedure created.

SQL> set timing on
SQL> exec loop_inter

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.06

3. Now compile in native mode:

SQL> alter session set plsql_code_type=native;

SQL> @plsql_native

Procedure created.

SQL> exec loop_native

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.15

4. Check contents of $ORACLE_HOME/plsql/user_shared_libs should look something like:

-rwxr-xr-x 1 oracle dba 5180 Feb 18 19:13 NATIVE__SYS__P__39965.so

to verify shared module got loaded:

# cat /proc/ /maps

To recompile an existing procedure to use native plsql, use:

SQL> alter session set plsql_code_type=native;
SQL> alter procedure compile;

OR without changing the init.ora parameter by using:

SQL> alter procedure native_proc compile plsql_code_type=native;

This entry was posted on Wednesday, April 1st, 2009 at 7:22 am and is filed under Oracle, Oracle Scripts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.