Create or replace package Evalua is

   Function Traduzca(formula in varchar2, csf in char default 'xX= ', csd in char default '**') return varchar2;

   Procedure Valor(formula in varchar2, resultado in out varchar2);

   Procedure Valor(formula in varchar2, resultado in out number);

   Procedure Valor(formula in varchar2, resultado in out date);

   Function Evaluable(Vtexto in varchar2, vfin in char default '=') return varchar2;
 
   Function Piezas(Vtexto in varchar2) return number;

   Function Agregado(Vclau in varchar2, Vfunc in varchar2 default 'count(*)') return number;

   Function Existe(Vclau in varchar2) return number;

   Procedure Ejecuta(vRutina in varchar2);

End Evalua;
/

Create or replace package Body Evalua is

   Function Traduzca(formula in varchar2, csf in char default 'xX= ', csd in char default '**') return varchar2 is
   begin
      return translate(formula, csf, csd);
   end Traduzca;

   Procedure Valor(formula in varchar2, resultado in out varchar2) IS
   BEGIN
      if formula is not null then
         EXECUTE IMMEDIATE
               'Select '||formula||' from dual'
            into resultado;
      end if;
   END Valor;

   Procedure Valor(formula in varchar2, resultado in out number) IS
   BEGIN
      if formula is not null then
         EXECUTE IMMEDIATE
               'Select '||formula||' from dual'
            into resultado;
      end if;
   END Valor;

   Procedure Valor(formula in varchar2, resultado in out date) IS
   BEGIN
      if formula is not null then
         EXECUTE IMMEDIATE
               'Select '||formula||' from dual'
            into resultado;
      end if;
   END Valor;

   Function Evaluable(Vtexto in varchar2, vfin in char default '=') return varchar2 is
      Vpos     number(4);
      Vresult  varchar2(400):=null;
   begin
      Vpos:=InStr(Vtexto, vfin);
      If Vpos>0 then
     	 Vresult:=substr(Vtexto, 1, Vpos-1);
     	 if translate(Vresult, 'z .0123456789*+xX'||chr(9), 'z') is not null then
            Vresult:=null;
         end if;
      end if;
      return translate(Vresult, 'xX ', '**');
   end Evaluable;


   Function Piezas(Vtexto in varchar2) return number is
      Vpos     number(4);
      Vpzs     number(4):=0;
      Vtex     varchar2(400):=null;
      Vsum     varchar2(400):=null;
   begin
      if Vtexto is not null then
         Vtex:=Vtexto||'+';
         Vpos:=InStr(Vtex,'+');
         while Vpos>0
         loop
            Vsum:=substr(Vtex, 1, Vpos-1);
            Vtex:=substr(Vtex, Vpos+1);
            if Vsum is not null then
               Vpos:=Instr(Vsum,'*');
               if Vpos>0 then
                  Vpzs:=Vpzs+nvl(to_number(substr(vsum, 1, Vpos-1)),0);
               else
                  Vpzs:=Vpzs+1;
               end if;
            end if;
            Vpos:=InStr(Vtex,'+');   	  	 	  
         end loop;
         return greatest(Vpzs, 1);
      else
         return 1;
      end if;
   end Piezas;

   Function Agregado(Vclau in varchar2, Vfunc in varchar2 default 'count(*)') return number is
      resultado number;
   begin
      if VClau is not null then
         EXECUTE IMMEDIATE
               'Select '||Vfunc||' from '||VClau
            into resultado;
      end if;
      return resultado;
   exception
      when no_data_found then
         return null;
   end Agregado;

   Function Existe(Vclau in varchar2) return number is
      resultado number(1);
   begin
      if VClau is not null then
         EXECUTE IMMEDIATE
               'Select 1 from '||VClau
            into resultado;
      end if;
      return 1;
   exception
      when no_data_found then
         return 0;
      when too_many_rows then
         return 2;
   end Existe;

   Procedure Ejecuta(vRutina in varchar2) Is
   Begin
      If vRutina Is Not Null Then
         EXECUTE IMMEDIATE vRutina;
      End If;
   End Ejecuta;

End Evalua;
/
