The DBMS_DDL package wraps a single PL/SQL unit, such as a package specification, package body, function, procedure, type specification, or type body.
It contains WRAP function and the CREATE_WRAPPED procedure.
Example: WRAP Function
If pl/sql unit is small, you can directly embed PL/SQL code into another PL/SQL code to wrap and compile it
Execute below script and you will same result as we have seen in part 1.
declare
sql_text_t dbms_sql.varchar2a;
sql_wrap_t dbms_sql.varchar2a;
begin
-- Store the pl/sql code in the array or pl/sql table
-- Each line of code will go into a new row
sql_text_t (1) := 'CREATE OR REPLACE FUNCTION get_sysdate RETURN VARCHAR2 AS ';
sql_text_t (2) := 'BEGIN ';
sql_text_t (3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
sql_text_t (4) := 'END get_sysdate;';
-- now compile and wrap the code
sql_wrap_t := sys.dbms_ddl.wrap (ddl => sql_text_t,
lb => 1,
ub => sql_text_t.count
);
-- display each line of the wrapped code
for i in 1 .. sql_wrap_t.count
loop
dbms_output.put_line (sql_wrap_t (i));
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
/
It contains WRAP function and the CREATE_WRAPPED procedure.
Example: WRAP Function
If pl/sql unit is small, you can directly embed PL/SQL code into another PL/SQL code to wrap and compile it
Execute below script and you will same result as we have seen in part 1.
declare
sql_text_t dbms_sql.varchar2a;
sql_wrap_t dbms_sql.varchar2a;
begin
-- Store the pl/sql code in the array or pl/sql table
-- Each line of code will go into a new row
sql_text_t (1) := 'CREATE OR REPLACE FUNCTION get_sysdate RETURN VARCHAR2 AS ';
sql_text_t (2) := 'BEGIN ';
sql_text_t (3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
sql_text_t (4) := 'END get_sysdate;';
-- now compile and wrap the code
sql_wrap_t := sys.dbms_ddl.wrap (ddl => sql_text_t,
lb => 1,
ub => sql_text_t.count
);
-- display each line of the wrapped code
for i in 1 .. sql_wrap_t.count
loop
dbms_output.put_line (sql_wrap_t (i));
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
/
Example: CREATE_WRAPPED procedure
Now if the PL/SQL unit is large having several thousand lines, in that case it is better to compile the code into the database first and then execute a PL/SQL code to wrap and compile it.
Suppose you have a packaged procedure name XXAK_WRAP_TEST_PKG, already compiled in DB.
Describe this package
select text
from all_source
where name = 'XXAK_WRAP_TEST_PKG'
and type = 'PACKAGE BODY'
and owner = 'APPS';
Now use the CREATE_WRAPPED procedure to wrap this program unit.
Execute the below script to do the job
--
declare
sql_text_t dbms_sql.varchar2a;
v_object_type varchar2 (40);
v_object_name varchar2 (60) := 'XXAK_WRAP_TEST_PKG';
cursor c_package_body (v_package_name varchar2)
is
select text
from all_source
where name = v_package_name
and type = 'PACKAGE BODY'
and owner = 'APPS';
cursor c_procedure (v_procedure varchar2)
is
select text
from all_source
where name = v_procedure
and type = 'PROCEDURE'
and owner = 'APPS';
begin
begin
select object_type
into v_object_type
from all_objects
where object_name = v_object_name
and status = 'VALID';
exception
when too_many_rows then
-- It is recommended to wrap only package body, so that other user can see the specification and use the public part of it
-- if it is more than one row object is having Specification and Body both
v_object_type := 'PACKAGE BODY';
when others then
dbms_output.put_line('Error while Checking Object Type: '||sqlerrm);
end;
if (v_object_type = 'PROCEDURE' or v_object_type = 'FUNCTION') then
open c_procedure (v_object_name);
-- get each line of code into each array row, i.e. pl/sql table
fetch c_procedure
bulk collect into sql_text_t;
close c_procedure;
elsif v_object_type = 'PACKAGE BODY' then
open c_package_body (v_object_name);
fetch c_package_body
bulk collect into sql_text_t;
close c_package_body;
end if;
if sql_text_t.count > 0 then
-- Code stored in the database does not contain the ddl text, create or replace, we have to add this explicitly
sql_text_t (1) := 'CREATE OR REPLACE ' || sql_text_t (1);
-- This will compile the PL/SQL unit again and finally wrap it
dbms_ddl.create_wrapped (ddl => sql_text_t,
lb => 1,
ub => sql_text_t.count
);
end if;
dbms_output.put_line(v_object_name||' is wrapped now.');
exception
when others then
dbms_output.put_line('Error while wrapping: '||sqlerrm);
end;
/
select text
from all_source
where name = 'XXAK_WRAP_TEST_PKG'
and type = 'PACKAGE BODY'
and owner = 'APPS';
Execute the below script to do the job
declare
sql_text_t dbms_sql.varchar2a;
v_object_type varchar2 (40);
v_object_name varchar2 (60) := 'XXAK_WRAP_TEST_PKG';
cursor c_package_body (v_package_name varchar2)
is
select text
from all_source
where name = v_package_name
and type = 'PACKAGE BODY'
and owner = 'APPS';
cursor c_procedure (v_procedure varchar2)
is
select text
from all_source
where name = v_procedure
and type = 'PROCEDURE'
and owner = 'APPS';
begin
begin
select object_type
into v_object_type
from all_objects
where object_name = v_object_name
and status = 'VALID';
exception
when too_many_rows then
-- It is recommended to wrap only package body, so that other user can see the specification and use the public part of it
-- if it is more than one row object is having Specification and Body both
v_object_type := 'PACKAGE BODY';
when others then
dbms_output.put_line('Error while Checking Object Type: '||sqlerrm);
end;
if (v_object_type = 'PROCEDURE' or v_object_type = 'FUNCTION') then
open c_procedure (v_object_name);
-- get each line of code into each array row, i.e. pl/sql table
fetch c_procedure
bulk collect into sql_text_t;
close c_procedure;
elsif v_object_type = 'PACKAGE BODY' then
open c_package_body (v_object_name);
fetch c_package_body
bulk collect into sql_text_t;
close c_package_body;
end if;
if sql_text_t.count > 0 then
-- Code stored in the database does not contain the ddl text, create or replace, we have to add this explicitly
sql_text_t (1) := 'CREATE OR REPLACE ' || sql_text_t (1);
-- This will compile the PL/SQL unit again and finally wrap it
dbms_ddl.create_wrapped (ddl => sql_text_t,
lb => 1,
ub => sql_text_t.count
);
end if;
dbms_output.put_line(v_object_name||' is wrapped now.');
exception
when others then
dbms_output.put_line('Error while wrapping: '||sqlerrm);
end;
/
Describe the package XXAK_WRAP_TEST_PKG again.
Done! :)
