• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

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 ")

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

Open in new window

log-file.txt
0
wemy
Asked:
wemy
2 Solutions
 
sdstuberCommented:
I wouldn't use utl_file at all,  I'd use an external table with one column "file_text"

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
0
 
Devinder Singh VirdiCommented:
Second option. You can use grep to find particular pattern and its occurrence. grep is much faster than reading whole file using shell script read function (I don't know the reason). Therefore you can save intermediate result and use that result with your oracle procedure.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
wemyAuthor Commented:
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?
0
 
sdstuberCommented:
No, pl/sql doesn't have a "seek" operation
0
 
leclaudeCommented:
You could make use of SQL functions REGEXP_INSTR, REGEXP_REPLACE, or REGEXP_SUBSTR, which use standard REGEXP expressions to find text.
0
 
sdstuberCommented:
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
0
 
wemyAuthor Commented:
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....
0
 
sdstuberCommented:
oh you're right,  I forgot about fseek.  I never use it.  Thanks for the reminder!

A random search through a million lines will always take a long time.  There's no just no way to get around that.
Can you change your file format so the lines you want will be in a known place?

or change the process that creates the file to copy the lines you want in a separate file
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now