Wrapping Utility - Part 2

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;
/

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;
/

Describe the package XXAK_WRAP_TEST_PKG again.

Done! :)