Oracle Illustrated

Feb 21 2010   8:46AM GMT

Migrating from 9i to 11g – Wrap Utility

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Usually WRAP utility enables to hide the PL/SQL code. From Oracle 10g R2 we can use DBMS_DDL package for this. There are 3 WRAP sub-programs available in DBMS_DDL (function overloading).

A) The source string is passed as a normal string

FUNCTION WRAP RETURNS VARCHAR2
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL VARCHAR2 IN

B) The source string is passed as a associative array

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(256)
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL TABLE OF VARCHAR2(256) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(32767)
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DDL TABLE OF VARCHAR2(32767) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN

In Oracle 9i

Oracle 9i
Wrap utility (available in 11g also)

create or replace procedure dummy2 as
begin
null;
end;
/

wrap iname=c:/dummy.sql oname=dummy3.sql

OUTPUT

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
25
59
Lo2gJ8C3lCuqm2DIriMeCiyorkkwg5nnm7+fMr2ywFxaPtfXOyEJuHQruMAy/tJepZmBCC0r
uIHH
Lcmmpv/qnj4=

Oracle 11g

Oracle 11g
A) Normal string

DBMS_DDL

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_source VARCHAR2(32767);
l_wrap VARCHAR2(32767);
BEGIN
l_source := ‘create or replace procedure dummy2 as ‘ ||
‘BEGIN ‘ ||
‘NULL; ‘ ||
‘END;’;

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source);
DBMS_OUTPUT.put_line(l_wrap);
END;
/

OUTPUT

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
25
59
Lo2gJ8C3lCuqm2DIriMeCiyorkkwg5nnm7+fMr2ywFxaPtfXOyEJuHQruMAy/tJepZmBCC0r
uIHH
Lcmmpv/qnj4=

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.82

B) Associative array (for both the functions that uses associative array)

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrap DBMS_SQL.VARCHAR2A;
BEGIN
l_source (1) := ‘create or replace procedure dummy2 as ‘;
l_source(2) := ‘ BEGIN NULL; END;’;

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source, lb=> 1, ub => l_source.count);
FOR i IN 1 .. l_wrap.count LOOP
DBMS_OUTPUT.put_line(l_wrap(i));
END LOOP;
END;
/

OUTPUT :

create or replace procedure dummy2 wrapped

a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abc
d
abcd
7
26
59
11TTBymVupEgy2K5qZjc3v3hktYwg5nnm7+fMr2ywFxaPtfXOyEJuHQrabjAMv7SXqWZgQgt
K7iB
xy3JpqY8t56h

PL/SQL procedure successfully completed.

3  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • askmsc
    Hi,

    I use the wrap on a set of procedures and function included in one file say Script.ora then i use the command wrap iname=Script.ora oname=Script.plb.

    How to do the same in oracle 11g

    15 pointsBadges:
    report
  • askmsc
    and if i execute the Script.plb which was wrapped using oracle 11g it gives an error of ORA-00900.Please let me know how to solve this issue.RegardsSri
    15 pointsBadges:
    report
  • Michael Tidmarsh
    Hi askmsc, I would post your question in our community forums so one of our experts can try to help you with your issue.
    48,375 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: