Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E10472-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

A PL/SQL Source Code Wrapping

This appendix explains what wrapping is, why you wrap PL/SQL code, and how to do it.

Topics:

Overview of Wrapping

Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code by making it more difficult for others to view it.

You can wrap PL/SQL 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 PL/SQL 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.

Note:

Wrapping a file that is already wrapped has no effect on the file.

Guidelines for Wrapping

Limitations of Wrapping

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.

The wrap utility does not need to connect to Oracle Database (in fact, it cannot connect to Oracle Database).

To run the wrap utility, enter the wrap command at your operating system prompt using this 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, which you typically run using SQL*Plus. If you omit the file extension, .sql is assumed. For example, these commands are equivalent:

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

You can specify a different file extension. For example:

wrap iname=/mydir/myfile.src

output_file is the name of the wrapped file to be created. If you omit the oname option, output_file has the same name as input_file, but with the extension .plb. For example, these commands are equivalent:

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

You can specify a different output file name and extension. For example:

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

Note:

If input_file is already wrapped, output_file is identical to input_file.

Topics:

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 ADTs are wrapped; their bodies are replaced by a scrambled form that the PL/SQL compiler understands.

These 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;

Running the wrap Utility

For example, assume that the wraptest.sql file contains:

CREATE PROCEDURE wraptest IS
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  all_emps  emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO all_emps FROM employees;
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);
  END LOOP;
END;
/

To wrap the file, run this command from the operating system prompt:

wrap iname=wraptest.sql

The output of the wrap utility is similar to:

PL/SQL Wrapper: Release 10.2.0.0.0 on Tue Apr 26 16:47:39 2005
Copyright (c) 1993, 2005, Oracle.  All rights reserved.
Processing wraptest.sql to wraptest.plb

If you view the contents of the wraptest.plb text file, the first line is CREATE PROCEDURE wraptest wrapped and the rest of the file contents is hidden.

You can run wraptest.plb in SQL*Plus to run the SQL statements in the file:

SQL> @wraptest.plb

After the wraptest.plb is run, you can run the procedure that was created:

SQL> CALL wraptest();

Limitations of the wrap Utility

  • The PL/SQL code to be wrapped cannot include substitution variables using the SQL*Plus DEFINE notation.

    Wrapped source code is parsed by the PL/SQL compiler, not by SQL*Plus.

  • The wrap utility removes most comments from wrapped files.

    See "Input and Output Files for the PL/SQL wrap Utility".

Wrapping PL/QL Code with DBMS_DDL Subprograms

The DBMS_DDL package contains procedures for wrapping a single PL/SQL unit, such as a package specification, package body, function, procedure, type specification, or type body. These overloaded subprograms provide a mechanism for wrapping dynamically generated PL/SQL units that are created in a database.

The DBMS_DDL package contains the WRAP functions and the CREATE_WRAPPED procedures. The CREATE_WRAPPED both wraps the text and creates the PL/SQL unit. When invoking the wrap procedures, use the fully qualified package name, SYS.DBMS_DDL, to avoid any naming conflicts and the possibility that someone might create a local package called DBMS_DDL or define the DBMS_DDL public synonym. The input CREATE OR REPLACE statement runs with the privileges of the user who invokes DBMS_DDL.WRAP or DBMS_DDL.CREATE_WRAPPED.

The DBMS_DDL package also provides the MALFORMED_WRAP_INPUT exception (ORA-24230) which is raised if the input to the wrap procedures is not a valid PL/SQL unit.

Note:

Wrapping a PL/SQL unit that is already wrapped has no effect on the unit.

Topics:

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_DDL package

DBMS_DDL.CREATE_WRAPPED Procedure

In Example A-1 CREATE_WRAPPED is used to dynamically create and wrap a package specification and a package body in a database.

Example A-1 Wrapping Package with DBMS_DDL.CREATE_WRAPPED Procedure

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';

Result is similar to:

TEXT
--------------------------------------------------------------------
PACKAGE emp_actions WRAPPED
a000000
1f
abcd
...
2 rows selected.

Limitation of the DBMS_DDL.WRAP Function

If you invoke DBMS_SQL.PARSE (when using an overload where the statement formal has data type VARCHAR2A or VARCHAR2S for text which exceeds 32767 bytes) on the output of DBMS_DDL.WRAP, then you must set the LFFLG parameter to FALSE. Otherwise DBMS_SQL.PARSE adds lines to the wrapped unit which corrupts the unit.