[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1644
  • Last Modified:

Oracle 9i SQL loader

I have file as follows:
"56789","Abc","","Bcde","28"
"456789","Xyz","A","Mno","30"

I want to split first field into 3 different parts as

567 first field
8 second field
9 third field

second record

4567 first field
8 second field
9 third field


I want to insert into a table using SQL loader which following fields. If i insert first record it should be as

first field: 567
second : 8
third : 9
fourth : Abc
fifth :
sixth : Bcde
seventh : 28

Can you help me out with the code.

Thanks in advance
0
aaba646
Asked:
aaba646
  • 2
  • 2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try something like this

LOAD DATA
INFILE a.dat
APPEND INTO TABLE dept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( col1 char "substr(:col1,1,length(:col1)-2)",
  col2 char "substr(:col1,-2,1)",
  col3 char "substr(:col1,-1)",
  col4 char,..
)

You can also write functions and call the same with EXPRESSION from the control file.

see the below link for more information (search for EXPRESSION if you want to write a function and call that if the above does not work ).

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm#1007849

Thanks
0
 
aaba646Author Commented:
Iam able to load first three fields but the other fields iam not able to load properly.
Its treating different fields and loading differently.

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok...try as shown below :

LOAD DATA
INFILE a.dat
APPEND INTO TABLE dept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( col1 char "substr(:col1,1,length(:col1)-2)",
  col4 char,
  col5 char,
  col6 char,
  col7 char,
  col2 char "substr(:col1,-2,1)",
  col3 char "substr(:col1,-1)"
)

Thanks
0
 
aaba646Author Commented:
Thanks nuv. Its working!!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now