hello99hello
Programmer
Hello All,
I have a file(loaddata.dat) with the following sample record below:
Here are some facts about current line formart.
-There are three lines, like below, for each employee.
-each line for each employee always begins with (2 spaces) RWP20004.
-each line for each emoployee always ends with rwpx99.
-Each line includes spaces and other character such as + and X.
DP1 080 000
RWP20004 SM000016721788446C05102A 21875345804015240800++000000000+ 71
RWP20004 00000014768200+S2 0050102009583405833652+ 005258403840101
RWP2000400000+000000000+000000000+000000000+0000055834500000000+00RWPX99
-I have decided to create a temp table like following:
CREATE TABLE ADP_EXTRACTS_TEMP_100 (
FILE_NUMBER NUMBER(6),
RECORD_CODE VARCHAR2(3),
DATA_CONTROL_1 VARCHAR2(1),
DATA_CONTROL_2 VARCHAR2(1),
DATA_CONTROL_3 VARCHAR2(1)
)
-I also created a control file like following:
sqlldr p2k/p2k control=loaddata.ctl log=loaddata.log bad =loaddata.bad
data=loaddata.dat
MY OBJECTIVE is to extract fields from the each record into the table for each employee.
The fields, e.g. file number, can be from any of the three lines for each employee.
The fileds have to be loaded based on the position and length of each record.
here is my effort, but it is not working please help.
loaddata.ctl:
LOAD DATA
INTO
I have a file(loaddata.dat) with the following sample record below:
Here are some facts about current line formart.
-There are three lines, like below, for each employee.
-each line for each employee always begins with (2 spaces) RWP20004.
-each line for each emoployee always ends with rwpx99.
-Each line includes spaces and other character such as + and X.
DP1 080 000
RWP20004 SM000016721788446C05102A 21875345804015240800++000000000+ 71
RWP20004 00000014768200+S2 0050102009583405833652+ 005258403840101
RWP2000400000+000000000+000000000+000000000+0000055834500000000+00RWPX99
-I have decided to create a temp table like following:
CREATE TABLE ADP_EXTRACTS_TEMP_100 (
FILE_NUMBER NUMBER(6),
RECORD_CODE VARCHAR2(3),
DATA_CONTROL_1 VARCHAR2(1),
DATA_CONTROL_2 VARCHAR2(1),
DATA_CONTROL_3 VARCHAR2(1)
)
-I also created a control file like following:
sqlldr p2k/p2k control=loaddata.ctl log=loaddata.log bad =loaddata.bad
data=loaddata.dat
MY OBJECTIVE is to extract fields from the each record into the table for each employee.
The fields, e.g. file number, can be from any of the three lines for each employee.
The fileds have to be loaded based on the position and length of each record.
here is my effort, but it is not working please help.
loaddata.ctl:
LOAD DATA
INTO