Dynamic Wrapping of Procedures

In this topic we will discuss the all new dynamic wrapping from inside a PL/SQL block. This new feature is also born with Oracle Database 10g Release 2 version.

What is WRAP utility?

The wrap is a command line utility that wraps or encrypts the contents of a PL/SQL source file. The syntax for using this is:

wrap iname=input_file [oname=output_file]

Pass the source file name to iname parameter and specify the output file name (file to store the encrypted contents) in the oname parameter.

For Example:
wrap iname=myfunction.sql oname=myfunction.pld

If the oname parameter is not specified the wrap utility creates a wrapped file with same name as source file, but with extension as .pld

The new way of Wrapping
Starting with Oracle Database 10g Release 2, the wrapping gets a new shape. Two functions has been added in the DBMS_DDL package for wrapping.

1. DBMS_DDL.WRAP
2. DBMS_DDL.CREATE_WRAPPED

The DBMS_DDL.WRAP function simply wraps the source given and returns the wrapped source code. It does not create any procedures. The DBMS_DDL.CREATE_WRAPPED function is more or less equal to calling DBMS_DDL.WRAP function and thereafter calling EXECUTE IMMEDIATE with the wrapped source.

DBMS_DDL.WRAP
The syntax of the WRAP procedure is:

DBMS_DDL.WRAP(
ddl VARCHAR2,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN VARCHAR2;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2S;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2A;

For source which is less than the 32K size they can be defined as a VARCHAR2. For source greater than this size use the data types of DBMS_SQL.VARCHAR2S or DBMS_SQL.VARCHAR2A can be defined. They are collection variables.

Example of DBMS_DDL.WRAP procedure:

DECLARE
l_source VARCHAR2(32767);
l_wrapped VARCHAR2(32767);
BEGIN
l_source := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source := l_source || ' BEGIN ';
l_source := l_source ||' RETURN sysdate;';
l_source := l_source ||' END get_date;';

l_wrapped := DBMS_DDL.WRAP(ddl => l_source);

EXECUTE IMMEDIATE l_wrapped;
END;

The above example creates a function get_date in runtime as wrapped function.

For source size greater than 32 K, use the overloaded procedures of DBMS_DDL.WRAP. For source with size greater than 32 K the example is slightly modified as below:

DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrapped DBMS_SQL.VARCHAR2A;
BEGIN
l_source(1) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(2) := 'BEGIN ';
l_source(3) := 'RETURN sysdate;';
l_source(4) := 'END get_date;';

l_wrapped := DBMS_DDL.WRAP(l_source, 1, l_source.count);

EXECUTE IMMEDIATE l_wrapped;
END;

The second and third parameters to WRAP procedure is lower bound and upper bound of collection variables. To restrict the header and footer section of the collections you can use the parameters as shown in the below variant using DBMS_SQL.VARCHAR2S:

DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';

l_wrapped := DBMS_DDL.WRAP(l_source, 2, 5);

EXECUTE IMMEDIATE l_wrapped;
END;

DBMS_DDL.CREATE_WRAPPED
The syntax of the CREATE_WRAPPED is same as that of WRAP procedure. See below the example of the CREATE_WRAPPED function:

DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';

l_wrapped := DBMS_DDL.CREATE_WRAPPED(l_source, 2, 5);

EXECUTE IMMEDIATE l_wrapped;
END;

The above example demonstrates two things. One is usage of CREATE_WRAPPED and another is the usage of only using part from the collection variables.

More references can be found at:
From Oracle site
From Oracle-Base site
From Puget Sound Oracle Users Group (PSOUG)
And from Google