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;