UTL_FILE Text search
Dear All,
I am currently working on an Oracle project (PL/SQL) requires using any mean to search inside a text file and read specific value depending on a pattern, this pattern location is varrying,...
for more clarification, i am trying to read from a log file (result from running of SQL Loader), i am trying to read number of rejected records, it is alwas come after the pattern "Total logical records rejected: ", but its location is changing every time (becaue of rejection reasons for each rejected record is written in this file),
now, i want a very fastway to search about the pattern "Total logical records rejected: " and read the value directly without seeking the whole file.
i want to read number of rejected records and the run end timestamp ("Run ended on ")
I am currently working on an Oracle project (PL/SQL) requires using any mean to search inside a text file and read specific value depending on a pattern, this pattern location is varrying,...
for more clarification, i am trying to read from a log file (result from running of SQL Loader), i am trying to read number of rejected records, it is alwas come after the pattern "Total logical records rejected: ", but its location is changing every time (becaue of rejection reasons for each rejected record is written in this file),
now, i want a very fastway to search about the pattern "Total logical records rejected: " and read the value directly without seeking the whole file.
i want to read number of rejected records and the run end timestamp ("Run ended on ")
SQL*Loader: Release 10.2.0.2.0 - Production on Tue Nov 4 08:59:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /export/home/ods/mods/control/ibclimtu.txt
Data File: /export/home/ods/mods/data/ibclimtu.txt
Bad
File: /export/home/ods/mods/logs/ibclimtu.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 150000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table QODS.CUSTOMER_PORTFOLIO, loaded when PORTFOLIO_NUMBER != BLANKS
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
-------------------------
----- ---------- ----- ---- ---- ---------------------
PORTFOLIO_NUMBER 1:7 7 CHARACTER
SQL string for column : "ltrim(:PORTFOLIO_NUMBER,0)"
CUSTOMER_ID 9:18 10 CHARACTER
CUSTOMER_NAME 20:59 40 CHARACTER
CUSTOMER_ADDRESS_1 61:100 40 CHARACTER
CUSTOMER_ADDRESS_2 103:142 40 CHARACTER
CUSTOMER_ADDRESS_3 145:184 40 CHARACTER
CUSTOMER_ADDRESS_4 186:225 40 CHARACTER
SYS_ACCOUNT_NUMBER 227:239 13 CHARACTER
CUSTOMER_BRANCH 242:244 3 CHARACTER
EFFECTIVE_DATE 246:255 10 DATE DD/MM/YYYY
CUSTOMER_CIS_NO 257:270 14 CHARACTER
AMAP_CUSTOMER_NO 273:280 8 CHARACTER
Record 1: Rejected - Error on table QODS.CUSTOMER_PORTFOLIO, column PORTFOLIO_NUMBER.
ORA-01400: cannot insert NULL into
("QODS"."CUSTOMER_PORTFOLIO"."PORTFOLIO_NUMBER")
Record 66: Discarded - failed all WHEN clauses.
Record 132: Discarded - failed all WHEN clauses.
Record 198: Discarded - failed all WHEN clauses.
Record 264: Discarded - failed
all WHEN clauses.
Record 330: Discarded - failed all WHEN clauses.
Record 396: Discarded - failed all WHEN clauses.
Record 462: Discarded - failed all WHEN clauses.
Record 528: Discarded - failed all WHEN clauses.
Record 594: Discarded -
failed all WHEN clauses.
Record 612: Rejected - Error on table QODS.CUSTOMER_PORTFOLIO.
ORA-00001: unique constraint (QODS.CUST_PORTFOLIO_PK) violated
Record 642: Rejected - Error on table QODS.CUSTOMER_PORTFOLIO.
ORA-00001: unique constraint (QODS.CUST_PORTFOLIO_PK) violated
Record 660: Discarded - failed all WHEN clauses.
Record 726: Discarded - failed all WHEN clauses.
Record 792: Discarded - failed all WHEN clauses.
Record 858: Discarded
- failed all WHEN clauses.
Record 924: Discarded - failed all WHEN clauses.
Record 990: Discarded - failed all WHEN clauses.
Record 1056: Discarded - failed all WHEN clauses.
Record 1122: Discarded - failed all WHEN clauses.
Record 1188:
Discarded - failed all WHEN clauses.
Record 4818: Discarded - failed all WHEN clauses.
Record 4884: Discarded - failed all WHEN clauses.
Record 4950: Discarded - failed all WHEN clauses.
Record 5016: Discarded - failed all WHEN clauses.
Record 5082: Discarded - failed all WHEN clauses.
Record 5148: Discarded - failed all WHEN clauses.
Record 5214: Discarded - failed all WHEN clauses.
Record 5280: Discarded - failed all WHEN clauses.
Record 5346: Discarded - failed all
WHEN clauses.
Record 5412: Discarded - failed all WHEN clauses.
Record 5478: Discarded - failed all WHEN clauses.
Record 5544: Discarded - failed all WHEN clauses.
Record 5610: Discarded - failed all WHEN clauses.
Record 5676:
Discarded - failed all WHEN clauses.
Record 125445: Discarded - failed all WHEN clauses.
Record 125446: Discarded - failed all WHEN clauses.
Record 125447: Discarded - failed all WHEN clauses.
Record 125448: Discarded - failed all WHEN
clauses.
Record 125449: Discarded - failed all WHEN clauses.
Record 125450: Discarded - failed all WHEN clauses.
Record 125451: Discarded - failed all WHEN clauses.
Record 125452: Discarded - failed all WHEN clauses.
Record 125453:
Discarded - failed all WHEN clauses.
Record 125454: Discarded - failed all WHEN clauses.
Record 125455: Discarded - failed all WHEN clauses.
Record 125456: Discarded - failed all WHEN clauses.
Record 125457: Discarded - failed all WHEN
clauses.
Record 125458: Discarded - failed all WHEN clauses.
Record 125459: Discarded - failed all WHEN clauses.
Record 125460: Discarded - failed all WHEN clauses.
Record 125461: Discarded - failed all WHEN clauses.
Record 125462:
Discarded - failed all WHEN clauses.
Record 125463: Discarded - failed all WHEN clauses.
Record 125464: Discarded - failed all WHEN clauses.
Record 125465: Discarded - failed all WHEN clauses.
Record 125466: Discarded - failed all WHEN
clauses.
Table QODS.CUSTOMER_PORTFOLIO:
123492 Rows successfully loaded.
15 Rows not loaded due to data errors.
1959 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space
allocated for bind array: 18688 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 125466
Total logical records rejected: 15
Total logical records discarded: 1959
Run
began on Tue Nov 04 08:59:38 2008
Run ended on Tue Nov 04 09:02:27 2008
Elapsed time was: 00:02:49.02
CPU time was: 00:00:02.63
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree that an external table is a better option for this than UTL_FILE, and it may be somewhat faster.
Sure, a "grep" command in the O/S (assuming the server O/S is UNIX or Linux) should be faster than an Oracle utility, but PL\SQL can't issue host commands (like: "grep") directly.
Sure, a "grep" command in the O/S (assuming the server O/S is UNIX or Linux) should be faster than an Oracle utility, but PL\SQL can't issue host commands (like: "grep") directly.
ASKER
Dear All,
I want to give you some hint and a solution i found but not tested yet,
i know almost number of characters of the footer of the log file, i think i can go directly to the end of the file (attributes.length then seek function) then seek reverse from down to up by the length of the footer as a start point then i can go anywhere within this footer...
all of this using native PL/SQL only, this is valid?
I want to give you some hint and a solution i found but not tested yet,
i know almost number of characters of the footer of the log file, i think i can go directly to the end of the file (attributes.length then seek function) then seek reverse from down to up by the length of the footer as a start point then i can go anywhere within this footer...
all of this using native PL/SQL only, this is valid?
No, pl/sql doesn't have a "seek" operation
You could make use of SQL functions REGEXP_INSTR, REGEXP_REPLACE, or REGEXP_SUBSTR, which use standard REGEXP expressions to find text.
The regexp_* functions assume the text has already been read in from the file by some means.
wemy, have you tried any of the suggestions provided?
You could use a java stored procedure to issue a grep or use java to read the file and return the results to you pl/sql
wemy, have you tried any of the suggestions provided?
You could use a java stored procedure to issue a grep or use java to read the file and return the results to you pl/sql
ASKER
Dear sdsstuber,
there is a seek operation in PL/SQL "UTL_FILE.FSEEK", I tested all suggested ways, each took very long file if the log file was very big, non of the suggested solutions provide a good performance on case of very big log file...
the REGEXP_... is fine but i need to loop all lines of the file and read it, toll 30 seconds for 100,000 lines...
i am, expecting more than 1000,000 lines in the log file....
there is a seek operation in PL/SQL "UTL_FILE.FSEEK", I tested all suggested ways, each took very long file if the log file was very big, non of the suggested solutions provide a good performance on case of very big log file...
the REGEXP_... is fine but i need to loop all lines of the file and read it, toll 30 seconds for 100,000 lines...
i am, expecting more than 1000,000 lines in the log file....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then do
select * from my_external_tbl
where file_text like 'Total logical records rejected:%'
or file_text like 'Run ended on%'
this will be much, much faster