Wrapping PL/SQL Code with DBMS_DDL Subprogram

 Here I am giving an example to wrap oracle plsql code with dbms_ddl subprogram.

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.

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.

Oracle has given few Guidelines for Wrapping as below:

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

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

There are few Limitations also:

• Wrapping is not a secure method for hiding passwords or table names.

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

• Wrapped PL/SQL 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

• It does not wrap PL/SQL content in anonymous blocks or triggers or non-PL/SQL code

Wrapping Package with DBMS_DDL.CREATE_WRAPPED Procedure,

DROP PACKAGE employee_actions;

DECLARE

  l_package_content  clob; -- text/clob for creating package spec and body

 

  FUNCTION get_sepc_content (pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

    RETURN 'CREATE or replace  PACKAGE ' || pkgname || ' AUTHID DEFINER AS

    PROCEDURE increase_salary (p_emp_id NUMBER, p_salary NUMBER, p_commission NUMBER);

    

    PROCEDURE promote_employee (p_emp_id NUMBER, p_job VARCHAR2);

      

    PROCEDURE change_manager (p_emp_id NUMBER, p_mgr_id NUMBER);

    END ' || pkgname || ';';

  END get_sepc_content;

 

  FUNCTION get_body_content (pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

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

     PROCEDURE increase_salary (p_emp_id NUMBER, p_salary NUMBER, p_commission NUMBER) 

IS

     BEGIN

       UPDATE emp

         SET sal = p_salary, comm = p_commission WHERE empno = p_emp_id;

       COMMIT;  

     END increase_salary;

      

     PROCEDURE promote_employee (p_emp_id NUMBER, p_job VARCHAR2) IS

     BEGIN

       UPDATE emp

         SET job = p_job WHERE empno = p_emp_id;

       COMMIT;  

     END promote_employee;

      

     PROCEDURE change_manager (p_emp_id NUMBER, p_mgr_id NUMBER) IS

     BEGIN

       UPDATE emp

         SET mgr = p_mgr_id WHERE empno = p_emp_id;

       COMMIT;  

     END change_manager;

   END ' || pkgname || ';';

  END get_body_content;

 

BEGIN

  l_package_content := get_sepc_content ('employee_actions');-- Generate package spec

  EXECUTE IMMEDIATE l_package_content;                -- Create package spec

  l_package_content := get_body_content ('employee_actions'); -- Generate package body

  SYS.DBMS_DDL.CREATE_WRAPPED (l_package_content);     -- Create wrapped package body

END;

/

Note: There is no legal way to unwrap a *.plb binary file (as said by Oracle). You are supposed to backup and keep your source files after wrapping them.

Output: Result is similar to,

Reference:

• A PL/SQL Source Code Wrapping

That's it.

Happy APEXing!!!...