SQL Loader related question

[rogers42]

Hi Folks,

I need help loading data using the sql loader

Sample Data:

Toledo,230-123456789,SABNJCKHQ,3250
Ottawa-arizona,240567890234,SABBMXCRN,2000

My control file looks as follows

LOAD DATA
TRUNCATE INTO TABLE TMP
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
field_1 CHAR "UPPERfield_1)",
field_2 CHAR "SUBSTRfield_2, 1, 3)",
field_3 CHAR "LTRIM(SUBSTRfield_2, 4), '-')",
field_4 CHAR "UPPER(TRIMfield_4))",
field_5 CHAR "UPPERfield_5)"
)

Output:

The first three fields are loaded properly, field_4 is skipped and field_5 is loaded. Using field_2 twice confuses the loader.

Can somebody please suggest a work around so all fields are loaded in their proper columns ?

Thanks in advance

rogers42

 

[SantaMufasa]

Rogers,

I, frankly, don't use SQL*Loader (directly) anymore. Instead, I use Oracle's external tables feature:

SQL> create directory MyFlatFiles as 'c:\dhunt\sqldba'
  2  /

Directory created.

create table tmp_in
        (field_1 varchar2(20)
        ,field_2 varchar2(20)
        ,field_4 varchar2(20)
        ,field_5 varchar2(20)
 )
organization external
(  type oracle_loader
   default directory MyFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by ','
    missing field values are null
   )
location ('Rogers.txt')
)
reject limit unlimited
/

Table created.

select * from tmp_in;

FIELD_1              FIELD_2              FIELD_4              FIELD_5
-------------------- -------------------- -------------------- -------
Toledo               230-123456789        SABNJCKHQ            3250
Ottawa-arizona       240567890234         SABBMXCRN            2000

2 rows selected.

create table tmp as
       select upper(field_1)               field_1
             ,substr(field_2,1,3)          field_2
             ,ltrim(substr(field_2,4),'-') field_3
             ,upper(trim(field_4))         field_4
             ,upper(field_5)               field_5
         from tmp_in
/

Table created.

col field_2 format a7
select * from tmp
/

FIELD_1              FIELD_2 FIELD_3           FIELD_4              FIELD_5
-------------------- ------- ----------------- -------------------- -------
TOLEDO               230     123456789         SABNJCKHQ            3250
OTTAWA-ARIZONA       240     567890234         SABBMXCRN            2000

2 rows selected.

Let us know if this method interests you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[taupirho]

Hmm, repeated your results on my Oracle 9i system. Looks like a bug to me, which doesn't really help you. However one way around this is to load only 4 fields (1,2,4 and 5)and have a view on your tmp table equal to these 4 columns from tmp plus LTRIM(SUBSTR(field_2, 4), '-')"

e.g
create view tmp_vw as
select field_1,field_2,LTRIM(SUBSTR(field_2, 4), '-')",
field_4,field_5
from tmp


In order to understand recursion, you must first understand recursion.

 

[rogers42]

Excuse the belated reply. Thanks for the helpful tips. I have already implemented the work around and will read up on external tables as suggested.

Thanks

rogers42