Re: Using external table to load files with fixed pattern

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 3 Mar 2006 21:33:40 +0100
Message-ID: <4408a863$0$21543$626a54ce@news.free.fr>

"suri" <suriawan_at_gmx.de> a écrit dans le message de news: 1141404771.638481.106790_at_z34g2000cwc.googlegroups.com...
|I use Oracle 9.2.0.7 on HP-UX 11i.
|
| Example of the file content:
|
| [12345] Error during loading the data
| Error in class xyz, cannot open the file
| Time: 20060202 10:00
| Process: 123
| -----------------------
| [2365] Problem with database
| ORA-xxxx unique constraint violation
| Data cannot be inserted
| Time: 20060202 11:00
| Process: 1283
| -----------------------
|
| I want to use external table to load those files into table with
| following structure:
|
| logID number
| logMessage varchar2(100)
| detailMessage varchar2(500)
| timestamp date
| processID number
|
| >From the example above I would like to have 2 rows in the table:
|
| 1st Row:
| logID = 12345
| logMessage = Error during loading the data
| detailMessage = Error in class xyz, cannot open the file
| timestamp = 20060202 10:00
| processID = 123
|
| 2nd Row:
| logID = 2365
| logMessage = Problem with database
| detailMessage = ORA-xxxx unique constraint violation \n Data cannot be
| inserted
| timestamp = 20060202 11:00
| processID = 1283
|
| Regards,
| Suri
|

SQL> create or replace directory my_dir as 'E:\TEMP'   2 /

Directory created.

SQL> create table my_ext_tab (line varchar2(2000))   2 organization external (

  3     type oracle_loader
  4     default directory my_dir
  5     access parameters (
  6        records delimited by newline
  7        nobadfile
  8        nologfile
  9        nodiscardfile
 10        fields terminated by '<whatever that does not occur in file>'
 11        missing field values are null
 12        (line)
 13        )
 14     location ('my_file.txt')
 15     )

 16 reject limit unlimited
 17 /

Table created.

Then you have your log file online:

SQL> col line format a50
SQL> select * from my_ext_tab;
LINE

---

[12345] Error during loading the data
Error in class xyz, cannot open the file Time: 20060202 10:00
Process: 123

---

[2365] Problem with database
ORA-xxxx unique constraint violation
Data cannot be inserted
Time: 20060202 11:00
Process: 1283

---

11 rows selected.

It's easy to insert into your final table with a PL/SQL block. But it's funnier to do it in SQL.
As it is a log file, i assume it has a fixed format:

- all lines are always present
- all fields in line are present
- all lines have the format you gave in your example.

SQL> col logid                    heading "LogID"
SQL> col logMessage    format a30 heading "LogMessage"
SQL> col detailMessage format a40 heading "DetailMessage"
SQL> col tim           format a16 heading "Timestamp"
SQL> col processid                heading "ProcessId"

SQL> with
  2 step1 as (

  3      select line, rownum rn,
  4             case when line = '-----------------------' then rownum end rn2
  5      from my_ext_tab

  6 ),
  7 step2 as (

  8      select line, rn,
  9             min(rn2) over (order by rn desc) group_nb
 10      from step1

 11 ),
 12 step3 as (

 13      select line, group_nb,
 14              row_number() over (partition by group_nb order by rn) rn
 15      from step2

 16 ),
 17 step4 as (

 18      select max(sys_connect_by_path(line,'/')) line
 19      from step3
 20      where line != '-----------------------'
 21      connect by prior group_nb = group_nb and prior rn = rn-1
 22      start with rn = 1
 23      group by group_nb

 24 )
 25 select to_number(substr(line,3,instr(line,']')-3)) logId,

 26         substr(line,instr(line,' ')+1,instr(line,'/',2)-instr(line,' ')-1)
 27           logMessage,
 28         replace(substr(line,instr(line,'/',2)+1,
 29                             instr(line,'/Time:')-instr(line,'/',2)-1),
 30                 '/','\n')
 31           detailMessage,
 32         to_char(to_date(substr(line,instr(line,'/Time: ')+7,
 33                                     instr(line,'/',instr(line,'/Time: ')+1)
 34                                     -instr(line,'/Time: ')-7),
 35                         'YYMMDD HH24:MI'),'DD/MM/YYYY HH24:MI') tim,
 36         to_number(substr(line,instr(line,'/Process: ')+10)) processid

 37 from step4
 38 /

     LogID LogMessage                     DetailMessage                            Timestamp         ProcessId
---------- ------------------------------ ---------------------------------------- ---------------- ----------
      2365 Problem with database          ORA-xxxx unique constraint violation\nDa 02/02/2006 11:00       1283
                                          ta cannot be inserted
     12345 Error during loading the data  Error in class xyz, cannot open the file 02/02/2006 10:00        123

2 rows selected.

Regards
Michel Cadot Received on Fri Mar 03 2006 - 14:33:40 CST