Wrapping PLSQL Source Code

Wrapping is the process of hiding PLSQL source code. Wrapping helps to protect your source code from business competitors and others who might misuse it. You can wrap PLSQL source code with either the wrap utility or DBMS_DDL subprograms. The wrap utility wraps a single source file, such as a SQL*Plus script. The DBMS_DDL subprograms wrap a single dynamically generated PLSQL unit, such as a single CREATE PROCEDURE statement.

Wrapped source files can be moved, backed up, and processed by SQL*Plus and the Import and Export utilities, but they are not visible through the static data dictionary views *_SOURCE.

Guidelines for Wrapping

1. Wrap only the body of a package or object type, not the specification. This allows other developers to see the information they must use the package or type, but prevents them from seeing its implementation.
2. Wrap code only after you have finished editing it. You cannot edit PL/SQL source code inside wrapped files. Either wrap your code after it is ready to ship to users or include the wrapping operation as part of your build environment. To change wrapped PL/SQL code, edit the original source file and then wrap it again.
3. Before distributing a wrapped file, view it in a text editor to be sure that all important parts are wrapped.

Limitations of Wrapping

1. Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.
2. Wrapping does not hide the source code for triggers. To hide the workings of a trigger, write a one-line trigger that invokes a wrapped subprogram.
3. Wrapping does not detect syntax or semantic errors. Wrapping detects only tokenization errors (for example, runaway strings), not syntax or semantic errors (for example, nonexistent tables or views). Syntax or semantic errors are detected during PL/SQL compilation or when executing the output file in SQL*Plus.
4. Wrapped PL/SQL units are not downward-compatible. Wrapped PL/SQL units are upward-compatible between Oracle Database releases, but are not downward-compatible. 

Wrapping PL/SQL Code with wrap Utility

The wrap utility processes an input SQL file and wraps only the PL/SQL units in the file, such as a package specification, package body, function, procedure, type specification, or type body. It does not wrap PL/SQL content in anonymous blocks or triggers or non-PL/SQL code.

To run the wrap utility, enter the wrap command at your operating system prompt using the following syntax (with no spaces around the equal signs):

wrap iname=input_file [ oname=output_file ]

input_file is the name of a file containing SQL statements, that you typically run using SQL*Plus. If you omit the file extension, an extension of .sql is assumed. For example, the following commands are equivalent:

wrap iname=/mydir/myfile

wrap iname=/mydir/myfile.sql

You can also specify a different file extension:

wrap iname=/mydir/myfile.src

output_file is the name of the wrapped file that is created. The defaults to that of the input file and its extension default is .plb. For example, the following commands are equivalent:

wrap iname=/mydir/myfile

wrap iname=/mydir/myfile.sql oname=/mydir/myfile.plb

You can use the option oname to specify a different file name and extension:

wrap iname=/mydir/myfile oname=/yourdir/yourfile.out

Input and Output Files for the PL/SQL wrap Utility

The input file can contain any combination of SQL statements. Most statements are passed through unchanged. CREATE statements that define subprograms, packages, or object types are wrapped; their bodies are replaced by a scrambled form that the PLSQL compiler understands.

The following CREATE statements are wrapped:

CREATE [OR REPLACE] FUNCTION function_name

CREATE [OR REPLACE] PROCEDURE procedure_name

CREATE [OR REPLACE] PACKAGE package_name

CREATE [OR REPLACE] PACKAGE BODY package_name

CREATE [OR REPLACE] TYPE type_name AS OBJECT

CREATE [OR REPLACE] TYPE type_name UNDER type_name

CREATE [OR REPLACE] TYPE BODY type_name

The CREATE [OR REPLACE] TRIGGER statement, and [DECLARE] BEGIN-END anonymous blocks, are not wrapped. All other SQL statements are passed unchanged to the output file.

All comment lines in the unit being wrapped are deleted, except for those in a CREATE OR REPLACE header and C-style comments (delimited by /* */). The output file is a text file, which you can run as a script in SQL*Plus to set up your PL/SQL subprograms and packages. Run a wrapped file as follows:

SQL> @wrapped_file_name.plb;

Using DBMS_DDL.CREATE_WRAPPED Procedure

In the below example CREATE_WRAPPED is used to dynamically create and wrap a package specification and a package body in a database.

Example: Using DBMS_DDL.CREATE_WRAPPED Procedure to Wrap a Package

DECLARE

  package_text VARCHAR2(32767); -- text for creating package spec & body

 

  FUNCTION generate_spec(pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

    RETURN 'CREATE PACKAGE ' || pkgname || ' AS

            PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);

            PROCEDURE fire_employee (emp_id NUMBER);

            END ' || pkgname || ';';

  END generate_spec;

 

  FUNCTION generate_body(pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

    RETURN 'CREATE PACKAGE BODY ' || pkgname || ' AS

            PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS

            BEGIN

              UPDATE employees

              SET salary = salary + amount WHERE employee_id = emp_id;

            END raise_salary;

           

            PROCEDURE fire_employee (emp_id NUMBER) IS

            BEGIN

              DELETE FROM employees WHERE employee_id = emp_id;

              END fire_employee;

            END ' || pkgname || ';';

  END generate_body;

 

BEGIN

  -- Generate package spec

  package_text := generate_spec('emp_actions')

 

  -- Create wrapped package spec

  DBMS_DDL.CREATE_WRAPPED(package_text);

 

  -- Generate package body

  package_text := generate_body('emp_actions');

 

  -- Create wrapped package body

  DBMS_DDL.CREATE_WRAPPED(package_text);

 

END;

-- Invoke procedure from wrapped package

CALL emp_actions.raise_salary(120, 100);

When you check the static data dictionary views *_SOURCE, the source is wrapped, or hidden, so that others cannot view the code details. For example:

SELECT text FROM USER_SOURCE WHERE name = 'EMP_ACTIONS';

The resulting output is similar to the following:

TEXT

--------------------------------------------------------------------

PACKAGE emp_actions WRAPPED

a000000

1f

abcd

...