problem with extract script

Hi experts, I need help with a script and a procedure. The script run every month end on ant server and is using a procedure in an oracle 7 database to extract all data from a specific schema from the database. This scripts tuns on 60 servers of different countries and extracts from the tables to a flat which gets ftp to a unix server to be uploaded in a different db. I will send a copy of the procedure and one for the script.

I have 3 questions about this method:

1) how can I make the procedure or the script create a file besides the table flat file and the logfile that includes the tablename and the amount of rows that ahs been extracted? Sometimes they state that the file is not complete and when I check I see that the file is complete so I want have a seperate file where I have the extracted record details for each table and if possible mailed to me so that I don't have to connect to each server to check this this out.

2) if the extract was not successfull, I want to receive an email, there is an email line in the script but it does not work. don't know why.

3) I want to convince the financial team that by extracted only the new and modified data each month is better that doing full extract of all the tables each month. I thought about creating a trigger that will fire as soon a new record is added or when a record is modified and send this info to a copy table(s) (I will have to create a copy for each table involved so that changes to each table is copied over) and at the end of the month only the data of  copy tables are extracted and ftp to the unix server.

if it is better to split these questions, please let me know.

***here is a copy of the procedure:

CREATE OR REPLACE procedure extractData(p_table     in varchar2,
                         p_schema    in varchar2,
                         p_dir       in varchar2,
                         p_filename  in varchar2,
                         p_separator in varchar2 default '^')
--authid current_user
as
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(1000) default '';
    l_status        integer;
    l_errorrows               number;
    l_colCnt        number default 0;
    l_separator     varchar2(1) default '';
    l_cnt           number default 0;
    l_sql             varchar2(100);

type refcursor is ref cursor;
l_cursor1 refcursor;
l_cursor2 refcursor;

begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w');
     l_sql      := 'select * from '||p_schema||'.'||p_table;

     dbms_sql.parse(  l_theCursor, l_sql, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i,
                                    l_columnValue, 1000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 1000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );

            -- transforms 8 or 9 zeros to NULLS
            -- replace multiple white spaces by a single one
            if l_columnValue = '00000000' or l_columnValue = '000000000' or l_columnValue = ' '
            then
               l_columnValue := '';
            elsif l_columnValue = '00000101'
            then
               l_columnValue := '00010101';
            elsif p_table = 'DOSSIER'
            then
               l_columnValue := replace(replace(replace(substr(l_columnValue,1,80), chr(10),' '),chr(13),' '),chr(94),'');
--                       select count(*) into l_errorrows from dual where l_columnValue like '%'||chr(13)||'%' or l_columnValue like '%'||chr(10)||'%';
--                    if l_errorrows = 1
--                    then
--                         select replace(replace(l_columnValue, chr(10),' '),chr(13),' ')
--                         into l_columnValue from dual;
--                    end if;
            elsif p_table = 'DOSSIER_PARTY'
                  or p_table = 'DOSSIER_GOODS'
                  or p_table = 'DOSSIER_CHARGES'
                  or p_table = 'DOSSIER_MILESTONES'
                  or p_table = 'DOSSIER_REMARKLOG'
                  or p_table = 'DOSSIER_CONTAINERS'
                  or p_table = 'CREDIT_CLAIM'
                  or p_table = 'PURCH_INVOICE'
                  or p_table = 'PURCH_INVOICE_LINES'
                  or p_table = 'B_RELATION'
                  or p_table = 'C_RELATION'
                  or p_table = 'RELATION_ADDRESS'
                  or p_table = 'RELATION_TYPES'
                  or p_table = 'ACCRUALS'
                  or p_table = 'INVLINES'
                  or p_table = 'INVOICE'
                  or p_table = 'JOURNAL'
                  or p_table = 'LOGGED_FLIGHTS'
            then
                  -- replace <CR> and <LF> characters in strings. Otherwise invalid lines get generated in output.
               l_columnValue := replace(replace(replace(l_columnValue, chr(10),' '),chr(13),' '),chr(94),' ');
               end if;

            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

***Here is copy of the script:

@echo off

set SID=%1
set SITE=%2
set LEVEL=%3
set SQLUSER=alm_select
set SQLPWD=askme4it
set FTPUSER=xsoo9
set FTPPWD=almextract
set WORKDIR=e:\oradata\admin\%SID%\scripts
set LOGFILE=e:\oradata\admin\%SID%\logbook\alm_ftp.log
set SQLDIR=c:\temp
set EXTDIR=c:\temp\ALM
set FTPDIR=%EXTDIR%\FTP
set DONEDIR=%EXTDIR%\DONE

for /f "tokens=1 delims=:" %%a in ('echo %SQLDIR%') do set SQLDRIVE=%%a:
for /f "tokens=1 delims=:" %%a in ('echo %WORKDIR%') do set WORKDRIVE=%%a:

for /f "tokens=1-7 delims=/-:, " %%t in ('echo exit^|cmd.exe /q /k "prompt $d $t"') do (
 for /f "tokens=2-4 delims=()/- skip=1" %%a in ('echo.^|date') do (
  set dow=%%t
  set %%a=%%u
  set %%b=%%v
  set %%c=%%w
  set hh=%%x
  set min=%%y
  set ss=%%z
 )
)

set TIMETAG=%yy%%mm%%dd%%hh%%min%
echo %TIMETAG% > %LOGFILE%
rem # Check parameters
rem # Is sid passed as parameter
if "%1" == "" goto NoSID

set ORACLE_SID=%SID%

set TARFILE=%SITE%_%TIMETAG%.TAR
if exist %EXTDIR%\%TARFILE% del /f %EXTDIR%\%TARFILE%

for /f %%a in ('type %WORKDIR%\%LEVEL%_tables.lst') do (
 
 echo if exist %SQLDIR%\%SITE%_%%a.txt del /f %SQLDIR%\%SITE%_%%a.txt > %WORKDIR%\cleanup.bat
 call %WORKDIR%\cleanup.bat
 del /f %WORKDIR%\cleanup.bat
 echo if exist %SQLDIR%\%SITE%_%%a.txt.gz del /f %SQLDIR%\%SITE%_%%a.txt.gz > %WORKDIR%\cleanup.bat
 call %WORKDIR%\cleanup.bat
 del /f %WORKDIR%\cleanup.bat

 echo alter session set NLS_DATE_FORMAT = 'YYYYMMDD'; > %WORKDIR%\alm.sql
 echo exec system.extractdata('%%a','BASIC','%SQLDIR%','%SITE%_%%a.txt','^^'^); >> %WORKDIR%\alm.sql
 echo exit >> %WORKDIR%\alm.sql
 plus33.exe %SQLUSER%/%SQLPWD% @%WORKDIR%\alm.sql >> %LOGFILE%
 del /f %WORKDIR%\alm.sql

 %WORKDIR%\gzip.exe %SQLDIR%\%SITE%_%%a.txt >> %LOGFILE%

 echo %SQLDRIVE% > %WORKDIR%\tarup.bat
 echo cd %SQLDIR% >> %WORKDIR%\tarup.bat
 echo if exist %EXTDIR%\%TARFILE% %WORKDIR%\tar.exe -rvf %EXTDIR%\%TARFILE% %SITE%_%%a.txt.gz >> %WORKDIR%\tarup.bat
 echo %WORKDRIVE% >> %WORKDIR%\tarup.bat
 echo cd %WORKDIR% >> %WORKDIR%\tarup.bat
 call %WORKDIR%\tarup.bat >> %LOGFILE%
 del /f %WORKDIR%\tarup.bat >> %LOGFILE%

 echo %SQLDRIVE% > %WORKDIR%\tarup.bat
 echo cd %SQLDIR% >> %WORKDIR%\tarup.bat
 echo if not exist %EXTDIR%\%TARFILE% %WORKDIR%\tar.exe -cvf %EXTDIR%\%TARFILE% %SITE%_%%a.txt.gz >> %WORKDIR%\tarup.bat
 echo %WORKDRIVE% >> %WORKDIR%\tarup.bat
 echo cd %WORKDIR% >> %WORKDIR%\tarup.bat
 call %WORKDIR%\tarup.bat >> %LOGFILE%
 del /f %WORKDIR%\tarup.bat >> %LOGFILE%

 del /f %SQLDIR%\%SITE%_%%a.txt.gz >> %LOGFILE%
)

move %EXTDIR%\%TARFILE% %FTPDIR%\%TARFILE% >> %LOGFILE%

echo open uxsfo10.alm.com > alm.ftp
echo user %FTPUSER% %FTPPWD% >> alm.ftp
echo binary >> alm.ftp
echo cd incoming >> alm.ftp
echo put %FTPDIR%\%TARFILE% >> alm.ftp
echo bye >> alm.ftp
ftp -i -n -s:alm.ftp >> %LOGFILE%

move %FTPDIR%\%TARFILE% %DONEDIR%\%TARFILE% >> %LOGFILE%

%WORKDIR%\blat194\blat.exe %LOGFILE% -to jmacnack.alm.com -subject "%SITE% alm report" -f noreply@alm.com -q -noh -priority 1 -server zwwl-eur-mail1 -try 3 -ti 60
del /f alm.ftp

exit

:NoSID
 echo TARGET DATABASE should be defined. Give TARGET DATABASE as parameter after scriptname.
exit