CODEWITHSUNDEEP
oracle-databasesql-loader
Shahin P
Shahin P

Reputation: 367

How to remove value of the column from flat files or replace column with some other value while load data from flat files in oracle tables

I have one temp table which is empty now. I want to load the data from that flat file to the oracle temp table. In one column col3 of the flat file mention as "X" but in the table i want to insert as "abc". If possible to remove column value from "X" in flat file then how it is possible? or replace value from "X" to "abc".

Upvotes: 0

Views: 1886

Answers (2)

Littlefoot
Littlefoot

Reputation: 142710

DECODE, right?

SQL> create table test (id number, col3 varchar2(20));

Table created.

SQL> $type test25.ctl
load data
infile *
replace into table test
fields terminated by ',' trailing nullcols
(
id,
col3 "decode(:col3, 'x', 'abc', :col3)"
)
begindata
1,xxx
2,yyy
3,x
4,123
SQL>
SQL> $sqlldr scott/tiger@orcl control=test25.ctl log=test25.log

SQL*Loader: Release 11.2.0.2.0 - Production on ╚et O×u 29 12:57:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL> select * From test order by id;

        ID COL3
---------- --------------------
         1 xxx
         2 yyy
         3 abc
         4 123

SQL>

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

SQL*Loader lets you apply SQL operators to fields, so you can manipulate the value from the file.

Let's say you have a simple table like:

create table your_table(col1 number, col2 number, col3 varchar2(3));

and a data file like:

1,42,xyz
2,42,
3,42,X

then you could make your control file replace an 'X' value in col3 with the fixed value 'abc' using a case expression:

load data
replace
into table your_table
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
  col1,
  col2,
  col3 "CASE WHEN :COL3 = 'X' THEN 'abc' ELSE :COL3 END"
)

Running that file through with that control file inserts three rows:

select * from your_table;

      COL1       COL2 COL
---------- ---------- ---
         1         42 xyz
         2         42    
         3         42 abc

The 'X' has been replaced, the other values are retained.

If you want to 'remove' the value, rather than replacing it, you could do the same thing but with null as the fixed value:

  col3 "CASE WHEN :COL3 = 'X' THEN NULL ELSE :COL3 END"

or you could use nullif or defaultif:

  col3 nullif(col3 = 'X')

Upvotes: 1

Related Questions