Wrapping PL/SQL code from within the database

If you have ever wanted to wrap PL/SQL  code that already exists within the database, then you can do so using the simple procedure below which uses a combination of DBMS_METADATA and DBMS_DDL.

Our reason for doing this was because our code is deployed directly from source control into the database and needs to be wrapped after deployment to stop casual viewing of the source. We didn’t want to have to stop wrapped code in source control, and didn’t have access to the wrap tool from the deployment server.

Here’s the procedure:

CREATE OR REPLACE PROCEDURE wrap_existing_pkg( p_pkg_name varchar2 )
  l_body      clob;   
  l_src       DBMS_SQL.VARCHAR2A;
  l_idx       number :=1;   
  l_amount    number := 32767;
  l_offset    number := 1;
  l_length    number;
  -- NOTE: Use of this package requires that the user has been granted
  --       execute permission on DBMS_DDL by SYS.

  l_body   := dbms_metadata.get_ddl( 'PACKAGE_BODY', p_pkg_name);                     
  l_length := dbms_lob.getlength( l_body );
  dbms_lob.open( l_body, 0);
  while ( l_offset < l_length ) loop            
    dbms_lob.read( l_body, l_amount, l_offset, l_src(l_idx) );
    l_offset := l_offset + l_amount;
    l_idx := l_idx+1;
  end loop;                
  dbms_lob.close( l_body );
  dbms_ddl.create_wrapped( l_src, 1, l_idx-1 );

To wrap a package body called ‘ENCRYPTION_TOOLS’ you would execute the following:

execute wrap_existing_pkg('ENCRYPTION_TOOLS');

That’s it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: