Procedure to move outline between instances or schemas

This procedure can be used to move outline between schemas and/ora databases.


CREATE OR REPLACE procedure SYSTEM.outline_get (source_inst in varchar2, outline_name in varchar2, source_owner varchar2, dest_owner varchar2)
is
TYPE typoutput_1 IS REF CURSOR;
replace_user varchar2(64);
output_1 typoutput_1;
sql_to_tune_orig long;
sql_to_tune long;
sql_to_run varchar2(4000);
outline_creator varchar2(4000);
outline_exists number;
outline_prv_exists number;
cursor replace_user_table is
select user_table_name from system.ol$hints where user_table_name is not null;
user_tbl_name varchar2(64);
user_tbl_name_fixed varchar2(64);

begin
/* checking whether temporary private outline exists, drop if exists */
    select count(*) into outline_prv_exists from system.ol$ where ol_name = 'PRV1_TMP';
        if outline_prv_exists > 0 then
            dbms_output.put_line ('Dropping private outline PRV1_TMP');
            execute immediate 'drop private outline PRV1_TMP';
        end if;

/* get sql statement the outline is created for and modify each schema occurance */
sql_to_run :=
    'select sql_text from dba_outlines@'||SOURCE_INST||' where name='''||outline_name||'''';
open output_1 for sql_to_run;
fetch output_1 into sql_to_tune_orig;
sql_to_tune := REPLACE(sql_to_Tune_orig,upper(SOURCE_OWNER),upper(DEST_OWNER));

/* Create temporary private outline for query */
execute immediate ('create private outline PRV1_TMP on '||sql_to_tune);

/* Replace rows from ol$hint from source */
execute immediate ('delete from system.ol$hints where ol_name = ''PRV1_TMP''');
COMMIT;
sql_to_run := 'insert into system.ol$hints select * from outln.ol$hints@'||SOURCE_INST||' where ol_name = '''||outline_name||'''';
execute immediate (sql_to_run);
COMMIT;
update system.ol$hints set ol_name = 'PRV1_TMP';
COMMIT;

/* Change USER_TABLE_NAME for each row */
open replace_user_table;
loop
fetch replace_user_table into user_tbl_name;
user_tbl_name_fixed := replace(user_tbl_name,upper(SOURCE_OWNER),upper(DEST_OWNER));
update system.ol$hints set user_table_name = user_tbl_name_fixed where user_table_name = user_tbl_name;
commit;
exit when replace_user_table%NOTFOUND;
end loop;

/* Refresh private outline after changes */
dbms_outln_edit.refresh_private_outline('PRV1_TMP');

/* Check if outline exists, skip is exists, create if no */
select count(*) into outline_exists from outln.ol$ where ol_name = ''||outline_name||'';
 if outline_exists > 0 then
 dbms_output.put_line ('PUBLIC WITH THIS NAME EXISTS!!!!!');
    else
    execute immediate ('create outline '||outline_name||' from private PRV1_TMP');
    execute immediate ('drop private outline PRV1_TMP');
 end if;
end;
/