Re: Can anyone assist with parsing log files

From: Rauf Sarwar <rsarwar_at_ifsna.com>
Date: 27 Jun 2002 21:59:54 -0700
Message-ID: <c2d690f2.0206272059.3ac35e23@posting.google.com>

"sfjones" <sfjones_at_bellsouth.net> wrote in message news:<V1sS8.10088$_87.1948273_at_e3500-atl1.usenetserver.com>...
> I am attempting to find a package to parse counts from log file created
> using sqlloader.
> I am looking for a way to match the counts loaded using sql loader to the
> counts of the table itself that was loaded.
>
>
> Thanks
>
> FJ

Don't know if this is something you are referring to.

SET SERVEROUT ON
CREATE OR REPLACE PROCEDURE TEST_COUNT IS

   hFile_      UTL_FILE.FILE_TYPE;
   dir_        VARCHAR2(512) := '&UtlFilePath';
   file_       VARCHAR2(50) := '&SqlLoaderLogFile';
   line_       VARCHAR2(1024);

   tbl_Count_ NUMBER;
   log_count_ VARCHAR2(10);
   CURSOR cur_ IS

      SELECT count(*)
      FROM &Table;   

BEGIN
    hFile_ := UTL_FILE.fopen(dir_, file_, 'r');     LOOP

       BEGIN
          line_ := NULL;
          UTL_FILE.get_line(hFile_, line_);
          IF (INSTR(UPPER(line_), 'ROWS SUCCESSFULLY LOADED', 1, 1) != 0) THEN
             line_ := LTRIM(line_);
             UTL_FILE.FCLOSE(hFile_);
             EXIT;             
          END IF;
          EXCEPTION
             WHEN OTHERS THEN
                IF UTL_FILE.IS_OPEN(hFile_) THEN
                   UTL_FILE.FCLOSE(hFile_);
                   EXIT;
                END IF;   
       END;

    END LOOP;
    IF (line_ IS NULL) THEN

       DBMS_OUTPUT.PUT_LINE('Unable to extract load string');     ELSE

       OPEN cur_;
       FETCH cur_ INTO tbl_count_;
       CLOSE cur_;   
       log_count_ := SUBSTR(line_, 1, TO_NUMBER(INSTR(line_, ' ', 1, 1)));
       DBMS_OUTPUT.PUT_LINE('TABLE COUNT: ' || TO_CHAR(tbl_count_));
       DBMS_OUTPUT.PUT_LINE('LOG LOAD COUNT: ' || log_count_);

    END IF;
END;
/

HTH
//Rauf Sarwar Received on Thu Jun 27 2002 - 23:59:54 CDT