Re: sql*loader question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 11 Jul 2001 06:25:12 -0700
Message-ID: <9ihk3o030bl@drn.newsguy.com>

In article <Pine.LNX.4.33.0107111119280.20881-100000_at_thenut.eti.pg.gda.pl>, <kazelot_at_thenut.eti.pg.gda.pl says...
>
>I have a text file:
>Circuit ID: STF1 1-25 Date: 24 Apr 01
>Test Result: PASS Cable Type: Cat 5 UTP
>[page break]
>Circuit ID: STF1 1-24 Date: 24 Apr 01
>Test Result: PASS Cable Type: Cat 5 UTP
>[page break]
>
>I would like to import the data to oracle. Can someone suggest me
>the controlfile that would do it? I have no idea how to deal with it.
>
>Not to make Sybrand Bakker angry, I include my oracle version: 8.1.7.
>(BTW: Sybrand, thanks for the help with sending mail from oracle.)
>
>Thanks in advance,
>kazelot
>
>

You can use the concatenate operator to join three report lines into one logical line and then use POSITION to pick off the fields you want, something like:

LOAD DATA
INFILE *
concatenate 3
INTO TABLE t
REPLACE
(

circuit_id   position(12:42) "ltrim(rtrim(:circuit_id))",
dt           position(59:67) date "dd mon rr",
test_result  position(80:109) "ltrim(rtrim(:test_result))",
cable_type   position(121:134) "ltrim(rtrim(:cable_type))"

)
BEGINDATA

Circuit ID:       STF1  1-25              Date:           24 Apr 01
Test Result:      PASS                    Cable Type:     Cat 5 UTP
[page break]
Circuit ID:       STF1  1-24              Date:           24 Apr 01
Test Result:      PASS                    Cable Type:     Cat 5 UTP

[page break]

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Jul 11 2001 - 08:25:12 CDT