create or replace package body pkg_nkw_dpl timestamp '2006-07-07:10:10:10' is ------------------------------------------------------------------ -- 2006 DataPRO Developers - m@urelio ------------------------------------------------------------------ -- Version: 2.0.2 ------------------------------------------------------------------ -- Collections, Records, Variables, Constants, Exceptions, Cursors ------------------------------------------------------------------ ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- POP_EXP ------------------------------------------------------------------ procedure pop_exp is lv_path varchar2(248) := '/home/oracle/utlfile'; lv_nkw varchar2(8192); lv_adf varchar2(8192); lv_cad varchar2(8192); lv_cmp varchar2(8192); lv_est varchar2(8192); lv_fin varchar2(8192); lv_hfs varchar2(8192); lv_prd varchar2(8192); lv_prm varchar2(8192); lv_pub varchar2(8192); lv_tut varchar2(8192); lv_usr varchar2(8192); lv_vnd varchar2(8192); begin begin declare cursor pop_cur is select a.table_name from tabs a order by table_name; pop_row pop_cur%ROWTYPE; begin if (not pop_cur%ISOPEN) then open pop_cur; end if; << pop_loop >> loop fetch pop_cur into pop_row; exit pop_loop when pop_cur%NOTFOUND; if (pop_row.table_name like 'NKW%') then lv_nkw := lv_nkw || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_ADF%') then lv_adf := lv_adf || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_CAD%') then lv_cad := lv_cad || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_CMP%') then lv_cmp := lv_cmp || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_EST%') then lv_est := lv_est || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_FIN%') then lv_fin := lv_fin || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_HFS%') then lv_hfs := lv_hfs || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_PRD%') then lv_prd := lv_prd || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_PRM%') then lv_prm := lv_prm || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_PUB%') then lv_pub := lv_pub || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like 'TUT%') then lv_tut := lv_tut || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_USR%') then lv_usr := lv_usr || lower(pop_row.table_name) || ','; elsif (pop_row.table_name like '%VIP_VND%') then lv_vnd := lv_vnd || lower(pop_row.table_name) || ','; end if; end loop pop_loop; if (pop_cur%ISOPEN) then close pop_cur; end if; end; end; begin lv_nkw := 'tables=(' || substr(lv_nkw, 1, length(lv_nkw) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_nkw, 'expnkw.par', lv_path); lv_adf := 'tables=(' || substr(lv_adf, 1, length(lv_adf) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_adf, 'expadf.par', lv_path); lv_cad := 'tables=(' || substr(lv_cad, 1, length(lv_cad) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_cad, 'expcad.par', lv_path); lv_cmp := 'tables=(' || substr(lv_cmp, 1, length(lv_cmp) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_cmp, 'expcmp.par', lv_path); lv_est := 'tables=(' || substr(lv_est, 1, length(lv_est) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_est, 'expest.par', lv_path); lv_fin := 'tables=(' || substr(lv_fin, 1, length(lv_fin) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_fin, 'expfin.par', lv_path); lv_hfs := 'tables=(' || substr(lv_hfs, 1, length(lv_hfs) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_hfs, 'exphfs.par', lv_path); lv_prd := 'tables=(' || substr(lv_prd, 1, length(lv_prd) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_prd, 'expprd.par', lv_path); lv_prm := 'tables=(' || substr(lv_prm, 1, length(lv_prm) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_prm, 'expprm.par', lv_path); lv_pub := 'tables=(' || substr(lv_pub, 1, length(lv_pub) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_pub, 'exppub.par', lv_path); lv_tut := 'tables=(' || substr(lv_tut, 1, length(lv_tut) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_tut, 'exptut.par', lv_path); lv_usr := 'tables=(' || substr(lv_usr, 1, length(lv_usr) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_usr, 'expusr.par', lv_path); lv_vnd := 'tables=(' || substr(lv_vnd, 1, length(lv_vnd) - 1) || ')'; pkg_nkw_hfs.fwrite(lv_vnd, 'expvnd.par', lv_path); end; exception when others then raise; end pop_exp; ------------------------------------------------------------------ -- POP_GRANTS ------------------------------------------------------------------ procedure pop_grants is lv_path varchar2(248) := '/home/oracle/utlfile'; lv_file varchar2(32) := 'syngrants.sql'; begin begin declare cursor pop_cur is select 'TABLE' object_type, a.table_name object_name from tabs a where (a.table_name like 'VIP%' or a.table_name like 'TUT%') union all select 'VIEW' object_type, a.view_name object_name from user_views a where (a.view_name like 'VIW_VIP%' or a.view_name like 'VIW_TUT%') order by 1; pop_row pop_cur%ROWTYPE; begin if (not pop_cur%ISOPEN) then open pop_cur; end if; pkg_nkw_hfs.fwrite('set feedback off', lv_file, lv_path); << pop_loop >> loop fetch pop_cur into pop_row; exit pop_loop when pop_cur%NOTFOUND; pkg_nkw_hfs.fappend('drop public synonym ' || pop_row.object_name || ';', lv_file, lv_path); pkg_nkw_hfs.fappend('create public synonym ' || pop_row.object_name || ' for ' || pop_row.object_name || ';', lv_file, lv_path); if (pop_row.object_type = 'TABLE') then pkg_nkw_hfs.fappend('grant select, insert, update, delete on ' || pop_row.object_name || ' to vip;', lv_file, lv_path); elsif (pop_row.object_type = 'VIEW') then pkg_nkw_hfs.fappend('grant select on ' || pop_row.object_name || ' to vip;', lv_file, lv_path); end if; end loop pop_loop; if (pop_cur%ISOPEN) then close pop_cur; end if; end; end; exception when others then raise; end pop_grants; end pkg_nkw_dpl; / show errors