Create or replace package Ejecuta is

   Function  Ddl(Pcom in varchar2) return varchar2;
   Function  Dml(Pcom in out varchar2) return varchar2;
   Procedure Omita(Pcom in out varchar2);
   Procedure ActDet     (pact in number, plinea in number, pinst in varchar2);
   Procedure ActCliente (psis in varchar2, pact in number, plinea in number);
   Function Siguiente(pCom in varchar2) return varchar2;

End Ejecuta;
/

Create or replace package Body Ejecuta is

   vPos    number(4);
   vFin    varchar2(5):=chr(10)||'/'||chr(10);

   Function Siguiente(pCom in varchar2) return varchar2 is
      vResult varchar2(4000);
   begin
      vPos:=Instr(pCom||vFin, vFin);
      if vPos>0 then
         vResult:=ltrim(rtrim(translate(substr(pCom, 1, vPos-1),chr(9)||chr(10)||chr(13),'   ')));
         if substr(vResult, length(vResult))=';' and upper(substr(vResult,greatest(length(vResult)-4,1)))!=' END;' then
            vResult:=substr(vResult, 1, length(vResult)-1);
         end if;
      else
         vResult:=null;
      end if;
      return vResult;
   end;

   Function Ddl(Pcom in varchar2) return varchar2 is
   begin
       EXECUTE IMMEDIATE
               pCom;
       return null;
   exception
      when others then
         return sqlerrm;
   end Ddl;

   Function Dml(Pcom in out varchar2) return varchar2 is
      Vresult varchar2(1024):=null;
      Vlinea  varchar2(4000);
   begin
      while pCom is not null and Vresult is null
      loop
         vLinea:=Siguiente(pCom);
         if vLinea is not null then
            vResult:=Ddl(vLinea);
            if vResult is null then
               pCom:=substr(pCom, vPos+length(vFin));
            end if;
         else
            pCom:=substr(pCom, vPos+length(vFin));
         end if;
      end loop;
      return vresult;
   end Dml;

   Procedure Omita(Pcom in out varchar2) is
      Vlinea  varchar2(4000);
   begin
      vLinea:=Siguiente(pCom);
      pCom:=substr(pCom, vPos+length(vFin));
   end;

   Procedure ActDet (pact in number, plinea in number, pinst in varchar2) IS
      pragma autonomous_transaction;
   BEGIN
      Update pryActDet
      set    instruccion   = pinst
      where  actualizacion = pact
      and    linea         = plinea;
 
      commit;
   END ActDet;

   Procedure ActCliente (psis in varchar2, pact in number, plinea in number) IS
       pragma autonomous_transaction;
   BEGIN
      Update pryActCliente
      set    actualizacion = pact,
             linea         = plinea
      where  sistema = psis;

      if sql%notfound then
         insert into pryActCliente (sistema, actualizacion, linea)
         values (psis, pact, plinea);
      end if;
      commit;
   END ActCliente;

End Ejecuta;
/

SET SERVEROUTPUT ON

declare
   x varchar2(400):='END;'||chr(10)||
                    '/'||chr(10)||
                    'end;'||chr(10)||
                    '/'||chr(10)||CHR(10)||chr(10)||
                    '/'||chr(10)||
                    '/'||chr(10)||
                    '/'||chr(10);
BEGIN
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
   DBMS_OUTPUT.PUT_LINE(EJECUTA.SIGUIENTE(X));
   EJECUTA.OMITA(X);
END;
/

