"But if record length dosen't match then data gets shifted to another columns"
Sounds to me like you need to change the field terminator?
If the contents of your file is:
12345 MANOJ JADHAV
23456 ABCDEFGHIJK ABCDEFGHIJ
Then you can make " " (space) the field separator:
create table t (
x int,
y varchar2(20),
z varchar2(20)
) organization external (
type oracle_loader
default directory tmp
access parameters (
records delimited by newline
fields terminated by " "
)
location ('emps.dat')
) reject limit unlimited;
select * from t;
X Y Z
---------- -------------------- --------------------
12345 MANOJ JADHAV
23456 ABCDEFGHIJK ABCDEFGHIJ
If you do need to limit the total length there are a couple of options:
1. Leave the external table as-is. Add a where clause when loading to bypass the unwanted rows:
select * from t
where length(to_char(x) || y || z) <= 25;
X Y Z
---------- -------------------- --------------------
12345 MANOJ JADHAV
2. Update the external table so records too long are rejected. You can do this by adding a computed column with a max length of 25. Use the "column transforms" clause to define it as the concatenation of the fields:
drop table t purge;
create table t (
x int,
y varchar2(20),
z varchar2(20),
cols varchar2(25)
) organization external (
type oracle_loader
default directory tmp
access parameters (
records delimited by newline
fields terminated by " " (
x char(27),
y char(27),
z char(27)
)
column transforms (
cols from concat(x, y, z)
)
)
location ('emps.dat')
) reject limit unlimited;
select * from t;
X Y Z COLS
---------- -------------------- -------------------- -------------------------
12345 MANOJ JADHAV 12345MANOJJADHAV