How to Compile PL/SQL Natively and Wrap Source Code

By default PL/SQL is compiled to an intermediate form that is interpreted when it runs. Native compilation turns it into machine code instead, which can speed up code that does a lot of computation. A different compile-time option, wrapping, hides the source text of a unit so others who can see your schema cannot read your logic. This guide covers both.

Code for This Guide

The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

alter session set plsql_code_type = {native | interpreted};

alter {procedure | function | package} name compile plsql_code_type = native [reuse settings];

dbms_ddl.create_wrapped('create or replace ... source text');

Compile Natively

With PLSQL_CODE_TYPE set to NATIVE for the session, the next unit is compiled to machine code, and it can be switched back to INTERPRETED one unit at a time.

Example:

alter session set plsql_code_type = native;

create or replace function fare_with_tax (p_fare number) return number is
begin
  return round(p_fare * 1.05, 2);
end;
/
select name, type, plsql_code_type
from   user_plsql_object_settings
where  name = 'FARE_WITH_TAX';

select fare_with_tax(200) as with_tax from dual;

-- back to interpreted for this one unit
alter function fare_with_tax compile plsql_code_type = interpreted;

select plsql_code_type
from   user_plsql_object_settings
where  name = 'FARE_WITH_TAX';

Output:

Session altered.

Function FARE_WITH_TAX compiled

Errors for FUNCTION FARE_WITH_TAX:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00924: native compilation is not supported on this platform

NAME             TYPE        PLSQL_CODE_TYPE
________________ ___________ __________________
FARE_WITH_TAX    FUNCTION    NATIVE

   WITH_TAX
___________
        210

Function FARE_WITH_TAX altered.

PLSQL_CODE_TYPE
__________________
INTERPRETED

The test database here runs on an ARM64 machine, where native compilation is not available: Oracle records NATIVE as the setting but reports PLS-00924, and the function still works, returning 210. On platforms that support it, the same statements compile without the message. The last query shows the function back to INTERPRETED. The example drops it afterward.

When Native Helps

• Loops, arithmetic, and string handling in PL/SQL run faster natively.
• Code that mostly runs SQL statements gains little, since the SQL itself is not affected.
• USER_PLSQL_OBJECT_SETTINGS shows the code type of each unit; recompile with REUSE SETTINGS to keep it.

Wrap the Source

DBMS_DDL.CREATE_WRAPPED takes the CREATE statement as text and creates the unit with its source wrapped.

Example:

begin
  dbms_ddl.create_wrapped(
    'create or replace function secret_discount (p_fare number) return number is
     begin return round(p_fare * 0.87, 2); end;');
end;
/
select secret_discount(100) as discounted from dual;

select line, substr(text, 1, 60) as source from user_source
where  name = 'SECRET_DISCOUNT' and line <= 2;

Output:

PL/SQL procedure successfully completed.

   DISCOUNTED
_____________
           87

   LINE SOURCE
_______ ____________________________________
      1 function secret_discount wrapped
        a000000
        369
        abcd
        abcd
        abcd

The function works normally and returns 87, but USER_SOURCE shows only the word wrapped and encoded text instead of the code. The example drops it afterward.

For source files, the wrap command-line utility shipped with the database does the same: wrap iname=pricing.sql creates pricing.plb, which you deploy instead of the readable file.

Things to Know

• Wrapping hides code from casual reading; it is not encryption, so never rely on it to hide passwords or keys.
• Keep the unwrapped source in version control: a wrapped unit cannot be edited, only replaced.
• Wrap package bodies and leave package specifications readable, so callers can see the interface.

Related Guides

• How to Use PL/SQL Compiler Warnings
• How to Use Conditional Compilation in PL/SQL

Conclusion

PLSQL_CODE_TYPE = NATIVE compiles PL/SQL to machine code where the platform supports it, speeding up compute-heavy code. DBMS_DDL.CREATE_WRAPPED and the wrap utility hide a unit's source text. Both are settings you apply when compiling; neither changes what the code does.