Wrap PL/SQL code in the current user of an Oracle database 10g or higher

wrap_schema.sql

/*
* Copyright 2016 Philipp Salvisberg <[email protected]>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
*     http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
SET SERVEROUTPUT ON
DECLARE
   PROCEDURE wrap_and_install(in_code IN CLOB) IS
      co_chunksize INTEGER := 8196;
      l_array      dbms_sql.varchar2a;
      l_lb         INTEGER := 1;
      l_ub         INTEGER;
      l_cursor     PLS_INTEGER;
   BEGIN
      l_ub := ceil(sys.dbms_lob.getlength(in_code) / co_chunksize);
      <<clob_chunks>>
      FOR i IN l_lb .. l_ub
      LOOP
         l_array(i) := sys.dbms_lob.substr(lob_loc => in_code,
                                           offset  => (i - 1) * co_chunksize + 1,
                                           amount  => co_chunksize);
      END LOOP clob_chunks;
      l_cursor := sys.dbms_sql.open_cursor;
      sys.dbms_sql.parse(c             => l_cursor,
                         STATEMENT     => l_array,
                         lb            => l_lb,
                         ub            => l_ub,
                         lfflg         => FALSE,
                         language_flag => sys.dbms_sql.native);
      sys.dbms_ddl.create_wrapped(ddl => l_array, lb => l_lb, ub => l_ub);
      sys.dbms_sql.close_cursor(l_cursor);
   END wrap_and_install;
BEGIN
   <<unwrapped_sources>>
   FOR l_rec IN (SELECT TYPE AS original_type,
                        CASE TYPE
                           WHEN 'PACKAGE' THEN
                            'PACKAGE_SPEC'
                           WHEN 'PACKAGE BODY' THEN
                            'PACKAGE_BODY'
                           WHEN 'TYPE' THEN
                            'TYPE_SPEC'
                           WHEN 'TYPE BODY' THEN
                            'TYPE_BODY'
                           ELSE
                            TYPE
                        END AS object_type,
                        NAME AS object_name
                   FROM user_source
                  WHERE TYPE IN ('FUNCTION',
                                 'PROCEDURE',
                                 -- 'PACKAGE',
                                 'PACKAGE BODY',
                                 -- 'TYPE',
                                 'TYPE BODY',
                                 'LIBRARY')
                        AND line = 1
                        AND lower(text) NOT LIKE '% wrapped%'
                  ORDER BY type, name)
   LOOP
      <<try_to_wrap>>
      DECLARE
         l_ddl  CLOB;
         l_code CLOB;
      BEGIN
         l_ddl := sys.dbms_metadata.get_ddl(object_type => l_rec.object_type,
                                            NAME        => l_rec.object_name,
                                            SCHEMA      => USER);
         -- wrap with EDITIONABLE clause fails on 12.1.0.2.0 with: 
         --    ORA-24230: input to DBMS_DDL.WRAP is not a legal PL/SQL unit
         l_code := REPLACE(l_ddl, 'CREATE OR REPLACE EDITIONABLE ', 'CREATE OR REPLACE ');
         -- wrapping within database supported through DBMS_DDL since 10gR1
         wrap_and_install(in_code => l_code);
         sys.dbms_output.put_line(l_rec.original_type || ' ' || l_rec.object_name ||
                                  ' wrapped.');
      EXCEPTION
         WHEN OTHERS THEN
            sys.dbms_output.put_line(l_rec.original_type || ' ' || l_rec.object_name ||
                                     ' not wrapped because of ' || SQLERRM);
      END try_to_wrap;
   END LOOP unwrapped_sources;
END;
/