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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    28,885 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: