A Wrapping PL/SQL Source Code

Overview of Wrapping

Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code from business competitors and others who might misuse 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 compilation 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

• Wrap only the body of a package or object type, not the specification.

  This allows other developers to see the information they need to use the package or type, but prevents them from seeing its implementation.

• 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.

• Before distributing a wrapped file, view it in a text editor to be sure that all important parts are wrapped.

Limitations of Wrapping

• 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.

• 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.

• 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.

• Wrapped PL/SQL program units are not downward-compatible.

  Wrapped compilation units are upward-compatible between Oracle Database releases, but are not downward-compatible. For example, you can load files processed by the V8.1.5 wrap utility into a V8.1.6 Oracle Database, but you cannot load files processed by the V8.1.6 wrap utility into a V8.1.5 Oracle Database.

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

Topics:

• Input and Output Files for the PL/SQL wrap Utility

• Running the wrap Utility

• Limitations of the wrap Utility

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

SQL> @wrapped_file_name.plb;

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

wrap iname=wrap_test.sql

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 wrap_test.sql to wrap_test.plb

SQL> @wrap_test.plb

SQL> CALL wraptest();

Wrapping PL/QL Code with DBMS_DDL Subprograms

The DBMS_DDL package contains procedures for wrapping a single PL/SQL compilation 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 program 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 executes 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.

Topics:

• Using DBMS_DDL.CREATE_WRAPPED Procedure

• Limitation of the DBMS_DDL.WRAP Function

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

SELECT text FROM USER_SOURCE WHERE name = 'EMP_ACTIONS';

TEXT
--------------------------------------------------------------------
PACKAGE emp_actions WRAPPED
a000000
1f
abcd
...