create or replace package xrp is

   function val(x in varchar2) return varchar2;
   function val(x in date) return varchar2;
   function val(x in number) return varchar2;

end;
/

create or replace package body xrp is

   function val(x in varchar2) return varchar2 is
   begin
      if x is null then
         return 'null,';
      else
         return ''''||replace(x,'''','''''')||''',';
      end if;
   end;
   function val(x in date) return varchar2 is
   begin
      if x is null then
         return 'null,';
      else
         return 'to_date('''||to_char(x,'ddmmyyyy hh24miss')||''',''ddmmyyyy hh24miss''),';
      end if;
   end;
   function val(x in number) return varchar2 is
   begin
      if x is null then
         return 'null,';
      else
         return x||',';
      end if;
   end;

end;
/

create or replace package replicacion is

   Type RecSrv is
      Record (Estado    varchar2(30),
              UltimaVer timestamp);
   Type TabSrv is
      Table of RecSrv index by varchar2(30);

   Procedure FijarEstado(pServidor in varchar2, pEstado in varchar2);
   function EstadoServidor(pServidor in varchar2) return varchar2;
   function LapsoServidor(pServidor in varchar2) return varchar2;
   function ChequearServidor(pServidor in varchar2) return varchar2;
   procedure RevisarServidores(pEnLinea in boolean default false);
   Procedure Limpiar;

   function Actual(pNombre in varchar2) return number;
   function Siguiente(pNombre in varchar2) return number;
   procedure insertarCom(pServidor in varchar2, pRutina in varchar2, pTabla in varchar2, pTexto in varchar2);
   procedure insertarCom(pRutina in varchar2, pTabla in varchar2, pTexto in varchar2);
   procedure EjecutarCom(pServidor in varchar2, pRutina in varchar2, pTabla in varchar2, pTexto in varchar2,
                         pEnLinea in boolean default false);
   procedure EjecutarCom(pRutina in varchar2, pTabla in varchar2, pTexto in varchar2,
                         pEnLinea in boolean default false);
   procedure insertarErr(pServidor in varchar2, pRutina in varchar2, pTexto in varchar2,
                         pMensaje in varchar2);
   function replicarSig return number;

end;
/

create or replace package body replicacion is

   vSrvs         TabSrv;
   vLapsoChequeo interval day to second:=interval '0 0:5:0' day to second;

   Procedure FijarEstado(pServidor in varchar2, pEstado in varchar2) is
   begin
      vSrvs(pServidor).Estado:=pEstado;
      vSrvs(pServidor).UltimaVer:=localtimestamp;
   end FijarEstado;

   function EstadoServidor(pServidor in varchar2) return varchar2 is
      vEstado varchar2(30):=vSrvs(pServidor).Estado;
   begin
      if vEstado!='EnLinea' and (LocalTimeStamp-vSrvs(pServidor).UltimaVer)>vLapsoChequeo then
         vEstado:=ChequearServidor(pServidor);
         FijarEstado(pServidor, vEstado);
      end if;
      return vEstado;
   Exception
      when no_data_found then
         return '*Error*';
   end EstadoServidor;

   function LapsoServidor(pServidor in varchar2) return varchar2 is
   begin
      return vSrvs(pServidor).estado||' '||to_char(LocalTimeStamp-vSrvs(pServidor).ultimaVer,'dddd hh:mi:ss');
   Exception
      when no_data_found then
         return '*Error*';
   end LapsoServidor;

   function ChequearServidor(pServidor in varchar2) return varchar2 IS
      vX number(2);
   BEGIN
      EXECUTE IMMEDIATE 'Select 1 from dual@'||pServidor into vX;
      return 'EnLinea';
   Exception
      When others then
         return 'SinServ';
   END ChequearServidor;

   procedure RevisarServidores(pEnLinea in boolean default false) is
      vServ varchar2(30):=vSrvs.First;
   begin
      while vServ is not null
      loop
         if vSrvs(vServ).estado!='EnLinea' or pEnLinea then
            FijarEstado(vServ, ChequearServidor(vServ));
         end if;
         vServ := vSrvs.next(vServ);
      end loop;
   End RevisarServidores;

   Procedure Limpiar is
   begin
      vSrvs.Delete;
   end Limpiar;

   function Actual(pNombre in varchar2) return number is
      vConsec number(10);
   begin
      select valor into vConsec
      from repsecuencia
      where nombre=pNombre;
      return vConsec;
   exception
      when no_data_found then
         return 0;
   end;

   function Siguiente(pNombre in varchar2) return number is
      pragma autonomous_transaction;
      vConsec number(10);
   begin
      update repsecuencia
         set valor=valor+1
      where nombre=pNombre
      returning valor-1 into vConsec;
      commit;
      return vConsec;
   end;

   procedure insertarCom(pServidor in varchar2, pRutina in varchar2, pTabla in varchar2, pTexto in varchar2) is
      Vsec number(10):=Siguiente('REP');
   begin
      insert into repcomandos (secuencia, servidor, rutina, texto)
          values(vSec, pServidor, pRutina||pTabla, substr(pTexto,1,length(pTexto)-1));
   end;

   procedure InsertarCom(pRutina in varchar2, pTabla in varchar2, pTexto in varchar2) is
      cursor rutinas is
          select Servidor, paquete||'.'||pRutina rutina
          from repservidores
          where tabla=pTabla;
   begin
      for cmds in rutinas
      loop
         InsertarCom(cmds.servidor, cmds.rutina, pTabla, pTexto);
      end loop;
   end;

   procedure EjecutarCom(pServidor in varchar2, pRutina in varchar2, pTabla in varchar2, pTexto in varchar2,
                         pEnLinea in boolean default false) is
      vEje varchar2(1):='N';
   begin
      if EstadoServidor(pServidor)='EnLinea' or pEnLinea then
         begin
            execute immediate 'begin '||pRutina||pTabla||'@'||pServidor||'('||substr(pTexto,1,length(pTexto)-1)||'); end;';
            vEje:='S';
         exception
            when others then
               FijarEstado(pServidor, ChequearServidor(pServidor));
               if pEnlinea then
                  Raise;
               end if;
         end;
      end if;
      if vEje='N' and not pEnLinea then
         insertarCom(pServidor, pRutina, pTabla, pTexto);
      end if;
   end;

   procedure EjecutarCom(pRutina in varchar2, pTabla in varchar2, pTexto in varchar2,
                         pEnlinea in boolean default false) is
      cursor rutinas is
          select Servidor, paquete||'.'||pRutina rutina
          from repservidores
          where tabla=pTabla;
   begin
      for cmds in rutinas
      loop
         EjecutarCom(cmds.servidor, cmds.rutina, pTabla, pTexto, pEnLinea);
      end loop;
   end;

   procedure insertarErr(pServidor in varchar2, pRutina in varchar2, pTexto in varchar2,
                         pMensaje in varchar2) is
      Vsec number(10):=Siguiente('ERR');
   begin
      insert into reperrores (secuencia, servidor, rutina, texto, mensaje)
          values(vSec, pServidor, pRutina, pTexto, pMensaje);
   end;

   function replicarSig return number is
      vServidor  repcomandos.servidor%type;
      vRutina    repcomandos.rutina%type;
      vTexto     repcomandos.texto%type;
      vConsec    number(10);
      cursor     comandos is
         select  secuencia, servidor, rutina, texto
         from    repcomandos
         where   estadoservidor(servidor)='EnLinea'
         order   by secuencia
         for     update;
   begin
      open comandos;
      fetch comandos into vConsec, vServidor, vRutina, vTexto;
      if comandos%found then
         begin
            execute immediate 'begin '||vrutina||'@'||vServidor||'('||vtexto||'); end;';
         exception
            when others then
               insertarErr(vServidor, vRutina, vTexto, substr(sqlerrm,1,400));
               FijarEstado(vServidor, ChequearServidor(vServidor));
         end;
         delete repcomandos
         where secuencia=vConsec;
         close comandos;
         commit;
      else
         close comandos;
      end if;
      return vConsec;
   end;

begin
   for serv in (select distinct servidor from repservidores)
   loop
      FijarEstado(serv.Servidor, ChequearServidor(serv.servidor));
   end loop;
end;
/

create or replace package repsync is 

   procedure Replicar(pId in varchar2, pMod in number);

end;
/

create or replace package body repsync is

   procedure Replicar(pId in varchar2, pMod in number) is
      vConsec number(10);
      Vstatus pls_integer;
      Vmens   varchar2(40);
      VTrans  pls_integer:=0;
   begin
      loop
         vConsec:=Replicacion.ReplicarSig;
         vTrans:=Vtrans+1;
         if vConsec is null then
            dbms_alert.waitone('REPXXXNO', Vmens, vStatus, 5);
         end if;
         if vTrans>=pMod then
            vTrans:=0;
            Replicacion.RevisarServidores(false);
            VConsec:=Replicacion.Actual(pId);
            exit when vConsec>0;
         end if;
      end loop;
   end;

end;
/

create or replace package repinv is

   procedure actxxx(pX1 in varchar2, pz1 in date, pY1 in number);
   procedure delxxx(pX1 in varchar2, pz1 in date, pY1 in number);

end;
/

create or replace package body repinv is

   procedure actxxx(pX1 in varchar2, pZ1 in date, pY1 in number) is
   begin
      update yyy set y1=y1+pY1, z1=pz1, control='REP'
      where x1=pX1;
      if sql%notfound then
         insert into yyy (x1, z1, y1, control)
            values (pX1, pZ1, pY1, 'REP');
      end if;
   end;

   procedure delxxx(pX1 in varchar2, pZ1 in date, pY1 in number) is
   begin
      delete from yyy
      where x1=pX1;
   end;

end;
/

CREATE OR REPLACE TRIGGER REPLICA_XXX
AFTER INSERT OR UPDATE OR DELETE ON XXX
FOR EACH ROW
BEGIN
   If Inserting and :new.control='LOC' Then
      --Opcion ...InsertarCom('Servidor','repinv.act','xxx'...
      Replicacion.EjecutarCom('act','xxx',xrp.val(:new.x1)||xrp.val(:new.z1)||xrp.val(:new.y1));
   elsif Updating and :new.control='LOC' then
      Replicacion.EjecutarCom('act','xxx',xrp.val(:new.x1)||xrp.val(:new.z1)||xrp.val(:new.y1-:old.y1));
   elsif Deleting then
      Replicacion.EjecutarCom('del','xxx',xrp.val(:old.x1)||xrp.val(:old.z1)||xrp.val(-:old.y1));
   end if;
END;
/

VARIABLE jobno number;
BEGIN
   --DBMS_JOB.SUBMIT(:jobno, 'repsync.Replicar(''STP'', 20);', SYSDATE, null);
   COMMIT;
END;
/

--Para detener los jobs de replicacion
begin
   begin
      --insert into repsecuencia values ('STP', 1);
   exception
      when dup_val_on_index then
         update repsecuencia
            set valor=1
         where nombre='STP';
   end;
   commit;
end;
/

BEGIN
   --DBMS_JOB.REMOVE(4);
   COMMIT;
END;
/
