Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Wrap PL/SQL code in the current user of an Oracle database 10g or higher
/*
* Copyright 2016 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* 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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.