RE: UTL_FILE performance
- From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 17 Feb 2004 15:10:05 -0500
Try ....=20
CREATE OR REPLACE PROCEDURE read_asql_log (
p_filename IN VARCHAR2
, p_location IN VARCHAR2)
AS
fhandle UTL_FILE.FILE_TYPE;
strtstamp VARCHAR2 (8);
strrows VARCHAR2 (6);
strdescription VARCHAR2 (255);
v_logline VARCHAR2 (4000);
v_tstamp DATE;
v_rows_affected NUMBER;
v_description VARCHAR2 (255);
file_error EXCEPTION;
PRAGMA EXCEPTION_INIT (file_error, -20100);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
BEGIN
fhandle :=3D UTL_FILE.FOPEN (p_location, p_filename, 'r', 4000);
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
-- probably specified wrong LOCATION OR FILE NAME
RAISE_APPLICATION_ERROR (-20100, 'Check asql Log LOCATION AND =
existence');
WHEN OTHERS THEN RAISE;
END;
--
BEGIN
-- scroll to start of data
LOOP
UTL_FILE.GET_LINE (fhandle, v_logline);
EXIT WHEN SUBSTR(v_logline, 1, 8) =3D '--------';
END LOOP;
END;
LOOP
BEGIN
UTL_FILE.GET_LINE (fhandle, v_logline);
strtstamp :=3D SUBSTR(v_logline, 1, 8);
EXIT WHEN strtstamp =3D 'Time Use';
--
strrows :=3D SUBSTR(v_logline, 9, 16);
v_description :=3D SUBSTR(v_logline, 16, 255);
v_tstamp :=3D TO_DATE (TRIM (strtstamp), 'HH24:MI:SS');
v_rows_affected :=3D TO_NUMBER (TRIM (strrows) );
--
INSERT INTO asql_log (tstamp, rows_affected, description)
VALUES (v_tstamp, v_rows_affected, v_description);
EXCEPTION -- reached end =
of file
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
UTL_FILE.FCLOSE (fhandle);
END;
/
I have a procedure that reads 10046^8 trace files and analyzes them, it =
reads 208mb file in under 5 minutes and I haven't got time to optimize =
it.
Raj
-------------------------------------------------------------------------=
-------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
select standard_disclaimer from company_requirements;
QOTD: Any clod can have facts, having an opinion is an art !
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
