Wrapping a Stored Procedure

Question

I have created a Stored Procedure in Oracle (Via TOAD). I need to deliver this procedure to some other developers. All is need to do is Wrap the procedure so that at basic level he/she should not be able to view the code. At the same time, the developer should be able to create the procedure and execute/test it.

my procedure is saved in say FileName.sql whose content are like:

Create or Replace Procedure IS Declaration Begin Code End;

Now i want this FileName.sql to be wrapped. So that the encrypted file can be send to other to check in different environment.

Please help me in how to wrap, and then how the other guy will be able to create the procedure, and execute the same.

Thanks in advance.

Answer 1

The Oracle WRAP utility does exactly that - allows you to encode a stored procedure in a form suitable for shipping.
The wrapped code is as portable as source code, but cannot be examined as plain text.

You will need access to the command line using a suitable system user (i.e. one that has access to the Oracle binary commands e.g. sqlplus etc.) and the basic syntax is:

wrap iname=input_file [ oname=output_file ]

If you do not specify any extensions, the default for input is .sql and output is .plb
Once you have generated a .plb file, you can execute it against the database to create your stored procedure.

See:

• http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS016
• http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/c_wrap.htm

WARNING

WRAPencoded procedures are not completely secure - it is possible to "unwrap" them.
A better way of using WRAP is to put the procedure(s) you wish to protect in a package and WRAP the package definition.

Let's say your procedure is:

CREATE OR REPLACE PROCEDURE PR_OUTPUT_TEXT(
  P_TYPE      IN  CHAR,
  P_TEXT      IN  VARCHAR2
) IS
  V_TYPE          CHAR(3) := UPPER(P_TYPE);
BEGIN
  
  IF V_TYPE = 'LOG' THEN
    APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG, TO_CHAR(SYSDATE,'HH24:MI:SS')||' - '||P_TEXT);
  ELSE
    APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT, P_TEXT);
  END IF;
  
END PR_OUTPUT_TEXT;

you would normally call it using:

EXECUTE PR_OUTPUT_TEXT('LOG', 'Kittehz!!!')

In a package you would define the package body and the procedure thus:

CREATE OR REPLACE PACKAGE BODY USER.MYPACKAGE AS

  PROCEDURE PR_OUTPUT_TEXT(
    P_TYPE      IN  CHAR,
    P_TEXT      IN  VARCHAR2
  ) IS
  BEGIN
  
    IF V_TYPE = 'LOG' THEN
      APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG, TO_CHAR(SYSDATE,'HH24:MI:SS')||' - '||P_TEXT);
    ELSE
      APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT, P_TEXT);
    END IF;
  
  END PR_OUTPUT_TEXT;

END MYPACKAGE;

and you would call the package using:

EXECUTE USER.MYPACKAGE.PR_OUTPUT_TEXT('LOG', 'ERMAHGERD KERTERNS!!!')

Answer 2

From documentation, to be precise, this is what you need to do :

Running the wrap Utility

For example, assume that the wrap_test.sql file contains the following:

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 the following from the operating system prompt:

wrap iname=wrap_test.sql

The output of the wrap utility is similar to the following:

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
If you view the contents of the wrap_test.plb text file, the first line is CREATE PROCEDURE wraptest wrapped and the rest of the file contents is hidden.

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

SQL> @wrap_test.plb

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

SQL> CALL wraptest();

More information in docs