create or replace package body pkg_nkw_dvp
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 --------------------------
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   -- BUILD_DNT
   ------------------------------------------------------------------
   procedure build_dnt
   is
      i binary_integer;
   begin
      i := gt_data.first;
      if (i is not null) then
         gv_stmt := 'create or replace trigger dnt_' || lower(gt_data(i).table_name) || chr(10) ||
                    'before insert or update or delete on ' || lower(gt_data(i).table_name) || chr(10) ||
                    'for each row' || chr(10) ||
                    'begin' || chr(10) ||
                    '   ------------------------------------------------------------------' || chr(10) ||
                    '   -- 1998 Nulaya Knowledgements' || chr(10) ||
                    '   ------------------------------------------------------------------' || chr(10) ||
                    '   -- Dynamic Trigger Generator' || chr(10) ||
                    '   ------------------------------------------------------------------' || chr(10) ||
                    '   pkg_nkw_dnt.init_dnt(''' || upper(gt_data(i).table_name) || ''');' || chr(10);
      end if;
      << bind_loop >>
      while (i is not null) loop
         if (upper(gt_data(i).data_type) in ('DATE','NUMBER','VARCHAR2')) then
            gv_stmt := gv_stmt ||
                    '   pkg_nkw_dnt.bind_column(''' || lower(gt_data(i).column_name) || ''', :old.' ||
                    lower(gt_data(i).column_name) || ', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
         else
            gv_stmt := gv_stmt ||
                    '   --pkg_nkw_dnt.bind_column(''' || lower(gt_data(i).column_name) || ''', :old.' ||
                    lower(gt_data(i).column_name) || ', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
         end if;
         i := gt_data.next(i);
      end loop bind_loop;
      i := gt_data.first;
      if (i is not null) then
         gv_stmt := gv_stmt ||
                    '   pkg_nkw_dnt.parse_action;' || chr(10) ||
                    '   if (not dbms_standard.deleting) then' || chr(10);
      end if;
      << set_loop >>
      while (i is not null) loop
         if (upper(gt_data(i).data_type) in ('DATE','NUMBER','VARCHAR2')) then
            gv_stmt := gv_stmt ||
                      '      pkg_nkw_dnt.set_new(''' || lower(gt_data(i).column_name) ||
                                         ''', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
         else
            gv_stmt := gv_stmt ||
                      '      --pkg_nkw_dnt.set_new(''' || lower(gt_data(i).column_name) ||
                                         ''', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
         end if;
         i := gt_data.next(i);
      end loop set_loop;
      i := gt_data.first;
      if (i is not null) then
         gv_stmt := gv_stmt ||
                    '   end if;' || chr(10) ||
                    '   exception' || chr(10) ||
                    '      when others then raise;' || chr(10) ||
                    'end;' || chr(10);
         pkg_nkw_rpl.run_code(gv_stmt);
      end if;
      exception
         when others then raise;
   end build_dnt;
   ------------------------------------------------------------------
   ------------------------ Public Section --------------------------
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   -- POP_DNT
   ------------------------------------------------------------------
   procedure pop_dnt(fv_object in pkg_nkw_env.gv_char%TYPE := null)
   is
   begin
      begin
         declare cursor pop_cur is
                 select a.obj#, a.name
                   from sys.obj$ a
                  where (a.name like upper(nvl(fv_object,'VI%')))
                    and (a.owner# = pkg_nkw_sys.get_nkid)
                    and (a.type# = 2);
                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;
               pkg_nkw_rpl.run_code('lock table ' || pop_row.name || ' in exclusive mode nowait');
               gt_data.delete;
               gt_data := pkg_nkw_obj.get_columns(pop_row.name);
               build_dnt;
            end loop pop_loop;
            if (pop_cur%ISOPEN) then
               close pop_cur;
            end if;
         end;
      end;
      exception
         when others then raise;
   end pop_dnt;
   ------------------------------------------------------------------
   -- BUILD_VIEW
   ------------------------------------------------------------------
   procedure build_view(ft_view in gt_view%TYPE)
   is
      i binary_integer;
   begin
      i := ft_view.first;
      while (i is not null) loop
         pkg_nkw_rpl.run_code('create or replace view ' || ft_view(i).view_name || ' as ' || ft_view(i).text);
         i := ft_view.next(i);
      end loop;
      exception
         when others then raise;
   end build_view;
   ------------------------------------------------------------------
   -- GET_VIEWS
   ------------------------------------------------------------------
   procedure get_views
   is
   begin
      delete from nkw_viw;
      begin
         declare cursor get_cur is
                 select a.view_name, a.text
                   from user_views a
                  where (a.view_name like 'VIW%');
                get_row get_cur%ROWTYPE;
         begin
            if (not get_cur%ISOPEN) then
               open get_cur;
            end if;
            << get_loop >>
            loop
               fetch get_cur into get_row;
               exit get_loop when (get_cur%NOTFOUND);
               insert into nkw_viw(view_name, text)
               values(get_row.view_name, get_row.text);
            end loop get_loop;
            if (get_cur%ISOPEN) then
               close get_cur;
            end if;
         end;
      end;
      exception
         when others then raise;
   end get_views;
   ------------------------------------------------------------------
   -- POP_VIEWS
   ------------------------------------------------------------------
   procedure pop_views(fv_name in varchar2 := null)
   is
   begin
      begin
         declare cursor get_cur is
                 select a.view_name, a.text
                   from nkw_viw a
                  where (a.view_name like upper(nvl(fv_name,'%')));
                get_row get_cur%ROWTYPE;
         begin
            if (not get_cur%ISOPEN) then
               open get_cur;
            end if;
            << get_loop >>
            loop
               fetch get_cur into get_row;
               exit get_loop when (get_cur%NOTFOUND);
               begin
                  pkg_nkw_rpl.run_code('create or replace view ' || get_row.view_name ||
                                       ' as ' || get_row.text);
                  exception
                     when others then
                        dbms_output.put_line('Error Compiling: ' || get_row.view_name || chr(10) || sqlerrm);
               end;
            end loop get_loop;
            if (get_cur%ISOPEN) then
               close get_cur;
            end if;
         end;
      end;
      exception
         when others then raise;
   end pop_views;
end pkg_nkw_dvp;
/
show errors