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

E25519-09
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
PDF · Mobi · ePub

A PL/SQL Source Text Wrapping

You can wrap the PL/SQL source text for any of these stored PL/SQL units, thereby preventing anyone from displaying that text with the static data dictionary views *_SOURCE:

Note:

Wrapping text is low-assurance security. For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator's Guide.

A file containing wrapped PL/SQL source text is called a wrapped file. A wrapped file can be moved, backed up, or processed by SQL*Plus or the Import and Export utilities.

To produce a wrapped file, use either the PL/SQL Wrapper utility or a DBMS_DDL subprogram. The PL/SQL Wrapper utility wraps the source text of every wrappable PL/SQL unit created by a specified SQL file. The DBMS_DDL subprograms wrap the source text of single dynamically generated wrappable PL/SQL units.

Both the PL/SQL Wrapper utility and DBMS_DDL subprograms detect tokenization errors (for example, runaway strings), but not syntax or semantic errors (for example, nonexistent tables or views).

By default, the 11.2 PL/SQL compiler can use use wrapped packages that were compiled with the 9.2 PL/SQL compiler. To prevent the 11.2 PL/SQL compiler from using wrapped packages that were compiled with the 9.2 PL/SQL compiler, set the PL/SQL compilation parameter PERMIT_92_WRAP_FORMAT to FALSE. For more information about PERMIT_92_WRAP_FORMAT, see Oracle Database Reference. For more information about PL/SQL compilation parameters, see "PL/SQL Units and Compilation Parameters".

Topics

PL/SQL Source Text Wrapping Limitations

  • Wrapped files are not downward-compatible between Oracle Database releases.

    For example, you cannot load files produced by the V8.1.6 PL/SQL Wrapper utility into a V8.1.5 Oracle Database.

  • Wrapping PL/SQL source text is not a secure way to hide passwords or table names.

    For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator's Guide.

  • You cannot wrap the PL/SQL source text of triggers.

    To hide the implementation details of a trigger, put them in a stored subprogram, wrap the subprogram, and write a one-line trigger that invokes the subprogram.

PL/SQL Source Text Wrapping Guidelines

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

    Leaving the specification unwrapped allows other developers to see the information needed to use the package or type (see Example A-5). Wrapping the body prevents them from seeing the package or type implementation.

  • Wrap files only after you have finished editing them.

    You cannot edit wrapped files. If a wrapped file needs changes, you must edit the original unwrapped file and then wrap it.

  • Before distributing a wrapped file, view it in a text editor and ensure that all important parts are wrapped.

PL/SQL Source Text Wrapping with PL/SQL Wrapper Utility

Note:

Oracle recommends using PL/SQL Wrapper Utility version 10 or later.

The PL/SQL Wrapper utility takes a single SQL file (such as a SQL*Plus script) and produces an equivalent text file in which the PL/SQL source text of each wrappable PL/SQL unit is wrapped. (For the list of wrappable PL/SQL units, see the introduction to "PL/SQL Source Text Wrapping".)

The PL/SQL Wrapper utility cannot connect to Oracle Database. To run the PL/SQL Wrapper utility, enter this command at the operating system prompt (with no spaces around the equal signs):

wrap iname=input_file [ oname=output_file ]

input_file is the name of an existing file that contains any combination of SQL statements. output_file is the name of the file that the PL/SQL Wrapper utility creates—the wrapped file.

Note:

input_file cannot include substitution variables specified with the SQL*Plus DEFINE notation, because output_file is parsed by the PL/SQL compiler, not by SQL*Plus.

The PL/SQL Wrapper utility deletes from the wrapped file all comments except:

  • Comments in CREATE statement headers (that is, between CREATE and plsql_source in the syntax diagram in Oracle Database SQL Language Reference)

  • Comments delimited by /* and */

Note:

If input_file is a wrapped file, then input_file and output_file have identical contents.

The default file extension for input_file is sql. The default name of output_file is input_file.plb. Therefore, these commands are equivalent:

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

This example specifies a different file extension for input_file and a different name for output_file:

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

You can run output_file as a script in SQL*Plus. For example:

SQL> @myfile.plb;

Example A-1 shows the text of a SQL file, wraptest2.sql, that contains two wrappable PL/SQL units—the procedure wraptest and the function fibonacci. The file also contains two comments and a SQL SELECT statement.

Example A-1 SQL File with Two Wrappable PL/SQL Units

-- The following statement will not change. This comment will be deleted.
 
SELECT COUNT(*) FROM EMPLOYEES
/
 
/* The PL/SQL source text of the following two CREATE statements
will be wrapped. This commment will not be deleted. */
 
CREATE PROCEDURE wraptest AUTHID DEFINER 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;
/
 
CREATE OR REPLACE FUNCTION fibonacci (
  n PLS_INTEGER
) RETURN PLS_INTEGER
AUTHID DEFINER
IS
  fib_1 PLS_INTEGER := 0;
  fib_2 PLS_INTEGER := 1;
BEGIN
  IF n = 1 THEN                              -- terminating condition
    RETURN fib_1;
  ELSIF n = 2 THEN
    RETURN fib_2;                           -- terminating condition
  ELSE
    RETURN fibonacci(n-2) + fibonacci(n-1);  -- recursive invocations
  END IF;
END;
/

Example A-2 uses the PL/SQL Wrapper utility to wrap wraptest2.sql and shows the wrapped file, wraptest2.plb. The wrapped file shows that the utility deleted the comment that begins with -- and wrapped (made unreadable) the PL/SQL source text of the procedure wraptest and the function fibonacci, but did not change the SELECT statement or the comment delimited by /* and */.

Example A-2 Wrapping File with PL/SQL Wrapper Utility

Assume that the operating system prompt is >. Wrap the file wraptest.sql:

> wrap iname=wraptest2.sql

Result:

PL/SQL Wrapper: Release 11.2.0.1.0- Production on Wed Sep 15 08:10:15 2010
 
Copyright (c) 1993, 2009, Oracle.  All rights reserved.
 
Processing wraptest2.sql to wraptest2.plb

Contents of wraptest.plb:

SELECT COUNT(*) FROM EMPLOYEES
/
/* The PL/SQL source text of the following two CREATE statements
will be wrapped. This commment will not be deleted. */
CREATE PROCEDURE wraptest wrapped 
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
121 134
Pf3/wD+9ncRZhp3XxTMUO3yIRvswg+nQ7UhqfHRG2vg+SD7x9XzsDUFWbdwCJVEOLKBBRuH6
VMoRHfX6apzfyMkvWhzQLCYvAcq6Zu7++E7PrXNxUJzk/FZW8P9eRgyyyMFnDj53aP1cDje9
ZdGr2VmJHIw0ZNHBYhDdR+du5U5Yy47a6dJHXFW9eNyxBHtXZDuiWYTUtlnueHQV9iYDwE+r
jFn+eZm4jgDcTLTEzfmIVtPDRNhYCY3xhPo7vJeS8M1AvP+4xh9+uO35XsRIsRl1PTFVrGwg
6iuxETwA5Pu2mwx3
 
/
CREATE OR REPLACE FUNCTION fibonacci wrapped 
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
14a fb
e1Yq3QQJoEoNKIeJlbgLoLdSgogwgxDcf8vWfHSKbuowFOXFKoj9MqYGqWyRxeeCUVqNVIO1
ICqJa3yPr6e7z8GZpMH3J0Cx0uQ0B1JuysymdNDlzfTvb7QWsrLU4jGs3h8Mm49/L9nyO4Xh
Ae06nawFpOJIAYpBf9wBVC+ZrjU/nuEtokBqCce6HWIoF6rYgz0V0W/47x5KpOnQ2i7X3kFe
FR8K7jT7X58k8xK9uYlZv5LhV71a7A==
 
/

In SQL*Plus, Example A-3 runs the wrapped file wraptest.plb, creating the procedure wraptest and the function fibonacci; selects the text of the subprograms (which is wrapped and therefore unreadable), and then invokes the subprograms.

Example A-3 Running Wrapped File and Viewing Wrapped PL/SQL Units

SQL> -- Run wrapped file:
SQL> 
SQL> @wraptest2.plb
SQL> SELECT COUNT(*) FROM EMPLOYEES
  2  /
 
  COUNT(*)
----------
       107
 
1 row selected.
 
SQL> /* The PL/SQL source text of the following two CREATE statements
SQL> will be wrapped. This commment will not be deleted. */
SQL> CREATE PROCEDURE wraptest wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  7
 20  121 134
 21  Pf3/wD+9ncRZhp3XxTMUO3yIRvswg+nQ7UhqfHRG2vg+SD7x9XzsDUFWbdwCJVEOLKBBRuH6
 22  VMoRHfX6apzfyMkvWhzQLCYvAcq6Zu7++E7PrXNxUJzk/FZW8P9eRgyyyMFnDj53aP1cDje9
 23  ZdGr2VmJHIw0ZNHBYhDdR+du5U5Yy47a6dJHXFW9eNyxBHtXZDuiWYTUtlnueHQV9iYDwE+r
 24  jFn+eZm4jgDcTLTEzfmIVtPDRNhYCY3xhPo7vJeS8M1AvP+4xh9+uO35XsRIsRl1PTFVrGwg
 25  6iuxETwA5Pu2mwx3
 26  
 27  /
 
Procedure created.
 
SQL> CREATE OR REPLACE FUNCTION fibonacci wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  8
 20  14a fb
 21  e1Yq3QQJoEoNKIeJlbgLoLdSgogwgxDcf8vWfHSKbuowFOXFKoj9MqYGqWyRxeeCUVqNVIO1
 22  ICqJa3yPr6e7z8GZpMH3J0Cx0uQ0B1JuysymdNDlzfTvb7QWsrLU4jGs3h8Mm49/L9nyO4Xh
 23  Ae06nawFpOJIAYpBf9wBVC+ZrjU/nuEtokBqCce6HWIoF6rYgz0V0W/47x5KpOnQ2i7X3kFe
 24  FR8K7jT7X58k8xK9uYlZv5LhV71a7A==
 25  
 26  /
 
Function created.
 
SQL> 
SQL> -- Try to display procedure source text:
SQL> 
SQL> SELECT text FROM USER_SOURCE WHERE name='WRAPTEST';
 
TEXT
--------------------------------------------------------------------------------
PROCEDURE wraptest wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
121 134
Pf3/wD+9ncRZhp3XxTMUO3yIRvswg+nQ7UhqfHRG2vg+SD7x9XzsDUFWbdwCJVEOLKBBRuH6
VMoRHfX6apzfyMkvWhzQLCYvAcq6Zu7++E7PrXNxUJzk/FZW8P9eRgyyyMFnDj53aP1cDje9
ZdGr2VmJHIw0ZNHBYhDdR+du5U5Yy47a6dJHXFW9eNyxBHtXZDuiWYTUtlnueHQV9iYDwE+r
jFn+eZm4jgDcTLTEzfmIVtPDRNhYCY3xhPo7vJeS8M1AvP+4xh9+uO35XsRIsRl1PTFVrGwg
6iuxETwA5Pu2mwx3
 
 
1 row selected.
 
SQL> 
SQL> -- Try to display function source text:
SQL> 
SQL> SELECT text FROM USER_SOURCE WHERE name='FIBONACCI';
 
TEXT
--------------------------------------------------------------------------------
FUNCTION fibonacci wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
14a fb
e1Yq3QQJoEoNKIeJlbgLoLdSgogwgxDcf8vWfHSKbuowFOXFKoj9MqYGqWyRxeeCUVqNVIO1
ICqJa3yPr6e7z8GZpMH3J0Cx0uQ0B1JuysymdNDlzfTvb7QWsrLU4jGs3h8Mm49/L9nyO4Xh
Ae06nawFpOJIAYpBf9wBVC+ZrjU/nuEtokBqCce6HWIoF6rYgz0V0W/47x5KpOnQ2i7X3kFe
FR8K7jT7X58k8xK9uYlZv5LhV71a7A==
 
 
1 row selected.
 
SQL> 
SQL> BEGIN
  2    wraptest;  -- invoke procedure
  3    DBMS_OUTPUT.PUT_LINE('fibonacci(5) = ' || fibonacci(5));
  4  END;
  5  /
Emp Id: 198
Emp Id: 199
Emp Id: 200
Emp Id: 201
Emp Id: 202
Emp Id: 203
Emp Id: 204
Emp Id: 205
Emp Id: 206
Emp Id: 100
fibonacci(5) = 3
 
PL/SQL procedure successfully completed.
 
SQL> 

PL/SQL Source Text Wrapping with DBMS_DDL Subprograms

The DBMS_DDL package provides WRAP functions and CREATE_WRAPPED procedures, each of which wraps the PL/SQL source text of a single dynamically generated wrappable PL/SQL unit. The DBMS_DDL package also provides the exception MALFORMED_WRAP_INPUT (ORA-24230), which is raised if the input to WRAP or CREATE_WRAPPED is not a valid wrappable PL/SQL unit. (For the list of wrappable PL/SQL units, see the introduction to "PL/SQL Source Text Wrapping".)

Each WRAP function takes as input a single CREATE statement that creates a wrappable PL/SQL unit and returns an equivalent CREATE statement in which the PL/SQL source text is wrapped. For more information about the WRAP functions, see Oracle Database PL/SQL Packages and Types Reference.

Caution:

If you pass the statement that DBMS_DDL.WRAP returns to the DBMS_SQL.PARSE procedure whose formal parameter statement has data type VARCHAR2A, then you must set the lfflg parameter of DBMS_SQL.PARSE to FALSE. Otherwise, DBMS_SQL.PARSE adds lines to the wrapped PL/SQL unit, corrupting it. (For the syntax of DBMS_SQL.PARSE, see Oracle Database PL/SQL Packages and Types Reference.)

Each CREATE_WRAPPED procedure does what its corresponding WRAP function does and then runs the returned CREATE statement, creating the specified PL/SQL unit. For more information about the CREATE_WRAPPED procedures, see Oracle Database PL/SQL Packages and Types Reference.

Tip:

When invoking a DBMS_DDL subprogram, use the fully qualified package name, SYS.DBMS_DDL, to avoid name conflict if someone creates a local package named DBMS_DDL or defines the public synonym DBMS_DDL.

Note:

The CREATE statement that is input to a WRAP function or CREATE_WRAPPED procedure runs with the privileges of the user who invoked the subprogram.

Example A-4 dynamically creates a package specification (using the EXECUTE IMMEDIATE statement) and a wrapped package body, using a CREATE_WRAPPED procedure.

Example A-4 Creating Wrapped Package Body with CREATE_WRAPPED Procedure

DECLARE
  package_text  VARCHAR2(32767); -- text for creating package spec and body
 
  FUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN 'CREATE PACKAGE ' || pkgname || ' AUTHID DEFINER 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
  package_text := generate_spec('emp_actions');  -- Generate package spec
  EXECUTE IMMEDIATE package_text;                -- Create package spec
  package_text := generate_body('emp_actions');  -- Generate package body
  SYS.DBMS_DDL.CREATE_WRAPPED(package_text);     -- Create wrapped package body
END;
/

Example A-5 selects the text of the package that Example A-4 created, emp_actions, and then invokes the procedure emp_actions.raise_salary. If the package specification were wrapped, then the information needed to invoke the procedure would be unreadable, like the PL/SQL source text of the package body.

Example A-5 Viewing Package with Wrapped Body and Invoking Package Procedure

Select text of package:

SELECT text FROM USER_SOURCE WHERE name = 'EMP_ACTIONS';

Result:

TEXT
------------------------------------------------------------------------
 
PACKAGE emp_actions AUTHID DEFINER AS
      PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
      PROCEDURE fire_employee (emp_id NUMBER);
      END emp_actions;
PACKAGE BODY emp_actions wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
180 113
1fOVodewm7j9dBOmBsiEQz0BKCgwg/BKoZ4VZy/pTBIYo8Uj1sjpbEz08Ck3HMjYq/Mf0XZn
u9D0Kd+i89g9ZO61I6vZYjw2AuBidnLESyR63LHZpFD/7lyDTfF1eDY5vmNwLTXrFaxGy243
0lHKAzmOlwwfBWylkZZNi2UnpmSIe6z/BU2nhbwfpqd224p69FwYVXmFX2H5IMsdZ2/vWsK9
cDMCD1KEqOnPpbU2yXdpW3GIbGD8JFIbKAfpJLkoLfVxoRPXQfj0h1k=

Invoke raised_salary and show its effect:

DECLARE
  s employees.salary%TYPE;
BEGIN
  SELECT salary INTO s FROM employees WHERE employee_id=130;
  DBMS_OUTPUT.PUT_LINE('Old salary: ' || s);
  emp_actions.raise_salary(130, 100);
  SELECT salary INTO s FROM employees WHERE employee_id=130;
  DBMS_OUTPUT.PUT_LINE('New salary: ' || s);
END;
/

Result:

Old salary: 2800
New salary: 2900
 
PL/SQL procedure successfully completed.