create or replace package util is

   VEsqOri  varchar2(30);
   VEnlOri  varchar2(30);
   VEsqDes  varchar2(30);
   VPivOri  varchar2(30);
   VPivDes  varchar2(30);
   VModo    Varchar2(1):='A';

   Procedure FijarSesion(pEsqOri in varchar2, pEsqDes in varchar2, pEnlOri in varchar2 default null,
                         pPivOri in varchar2 default null, pPivDes in varchar2 default null,
                         pModo in varchar2 default 'A');
   Procedure Copia(pTabla in varchar2, pCon in varchar2 default null);

   Procedure CopiaMultiple(pTablas in varchar2, pCon in varchar2 default null);

end;
/

create or replace package body util is

   Function ListaColumnas(pOri in varchar2, pTabla in varchar2) return varchar2 is
      cursor cols is 
         select o.column_name
         from all_tab_columns o
         where o.owner=pOri
           and o.table_name=pTabla;
      vTex varchar2(512);
   Begin
      for c in cols
      loop
         vTex:=Vtex||', '||c.column_name||' ';
      end loop;
      return substr(vTex,2);
   End ListaColumnas;

   Function ListaColumnasComunes(pOri in varchar2, pDes in varchar2, pTabla in varchar2) return varchar2 is
      cursor cols is 
         select o.column_name
         from all_tab_columns o
         where o.owner=pDes
           and o.table_name=pTabla
           and exists (select 1 from all_tab_columns d
                       where d.owner=pDes and d.table_name=pTabla and d.column_name=o.column_name);
      vTex varchar2(512);
   Begin
      for c in cols
      loop
         vTex:=Vtex||', '||c.column_name||' ';
      end loop;
      return substr(vTex,2);
   End ListaColumnasComunes;

   Function Condicion(vCon in varchar2) return varchar2 is
   Begin
      if vCon is not null then
         return ' where '||vCon;
      else
         return null;
      end if;
   End Condicion;

   Procedure FijarSesion(pEsqOri in varchar2, pEsqDes in varchar2, pEnlOri in varchar2 default null,
                         pPivOri in varchar2 default null, pPivDes in varchar2 default null,
                         pModo in varchar2 default 'A') is
   Begin
      VEsqOri:=upper(nvl(pEsqOri,USER));
      VEsqDes:=upper(nvl(pEsqDes,USER));
      if pEnlOri is not null then
         VEnlOri:='@'||upper(pEnlOri);
      else
         VEnlOri:=null;
      end if;
      VPivOri:=pPivOri;
      VPivDes:=pPivDes;
      VModo:=PModo;
   End FijarSesion;

   Function ExtraeTabla(Ptablas in out varchar2) return varchar2 is
      vPos    number(4);
      vTabla  varchar2(30);
   Begin
      vPos:=instr(Ptablas||',', ',');
      if vPos<=31 then
         vTabla:=substr(Ptablas,1,vpos-1);
      else
         vTabla:=substr(Ptablas,1,30);
      end if;
      Ptablas:=substr(pTablas, vPos+1);
      return Vtabla;
   End ExtraeTabla;

   Procedure Copia(pTabla in varchar2, pCon in varchar2 default null) is
      vInsCols varchar2(512);
      vSelCols varchar2(512);
      vCond    varchar2(128);
      vDest    varchar2(60);
      vOrig    varchar2(60);
   Begin
      if Vmodo!='A' then
         VInsCols:=ListaColumnas(VEsqOri, upper(pTabla));
      elsif VEnlOri is not null then
         VInsCols:=ListaColumnas(VEsqDes, upper(pTabla));
      else
         VInsCols:=ListaColumnasComunes(vEsqOri, vEsqDes, upper(pTabla));
      end if;
      if VPivOri is not null and VPivDes is not null then
         vDest:=' '''||VPivDes||''' ';
         vOrig:=' '''||VpivOri||''' ';
         vSelCols:=replace(vInsCols, ' '||upper(pCon)||' ', vDest||upper(pCon)||' ');
         if Pcon is not null then
            vCond:=' where '||pCon||'='||vOrig;
         end if;
      Else
         vSelCols:=vInsCols;
         vCond:=Condicion(pCOn);
      End if;
      if Vmodo='A' then
         if vInsCols is  not null then
            execute immediate 'insert into '||vEsqDes||'.'||pTabla||' ('||vInsCols||') '||
                              ' select '||vSelCols||' from '||vEsqOri||'.'||pTabla||vEnlOri||Vcond;
         else
            raise_application_error(-20001,'No hay columnas');
         end if;
      else
         if vInsCols is  not null then
            begin
               execute immediate 'drop table '||vEsqDes||'.'||pTabla;
            exception
               when others then
                  dbms_output.put_line('ERROR <'||pTabla||'> '||sqlerrm);
            end;
            execute immediate 'create table '||vEsqDes||'.'||pTabla||' as '||
                              ' select '||vSelCols||' from '||vEsqOri||'.'||pTabla||vEnlOri||Vcond;
         else
            raise_application_error(-20001,'No hay columnas');
         end if;
      end if;
   Exception
      when others then
         dbms_output.put_line('ERROR <'||pTabla||'> '||sqlerrm);
   End Copia;

   Procedure CopiaMultiple(pTablas in varchar2, pCon in varchar2 default null) is
      Vtablas varchar2(512):=translate(PTablas,'z '||chr(9),'z');
      Vtabla  varchar2(30);
   Begin
      While Vtablas is not null
      loop
         VTabla:=ExtraeTabla(Vtablas);
         Copia(Vtabla, pCon);
      end loop;
   End CopiaMultiple;

end;
/
