Re: Using external table to load files with fixed pattern

From: suri <suriawan_at_gmx.de>
Date: 3 Mar 2006 08:52:51 -0800
Message-ID: <1141404771.638481.106790@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 Received on Fri Mar 03 2006 - 10:52:51 CST