LOAD DATA CONTROL FILE

[hello99hello]

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

 

[ddrillich]

Can you please re-submit the control file?

 

[hello99hello]

Sorry, here is my sample control file.

Please any help would be appreciated.

Note any of the filed could be in any of the 3 existing lines for each employee.

LOAD DATA
APPEND INTO ADP_EXTRACT_TEMP_100
(

FILE_NUMBER "substrloaddata.dat 6,11)"
RECORD_CODE "substrloaddata.dat 12,14)"
DATA_CONTROL_1 "substrloaddata.dat 835, 835)"
DATA_CONTROL_2 "substrloaddata.dat 1203, 1203)"
DATA_CONTROL_3 "substrloaddata.dat 907, 907)"
)

 

[hello99hello]

I havwe modified my control my control file to be:
LOAD DATA
APPEND INTO TABLE ADP_EXTRACT_TEMP_100
(
FILE_NUMBER "substrloaddata.dat, 6,11 )",
RECORD_CODE "substrloaddata.dat 12,14 )",
DATA_CONTROL_1 "substrloaddata.dat, 56,56 )",
DATA_CONTROL_2 "substrloaddata.dat, 57,57 )",
DATA_CONTROL_3 "substrloaddata.dat, 58,58 )"
)

It is giving me error:
SQL*Loader-941: Error during describe of table ADP_EXTRACT_TEMP_100
ORA-04043: object ADP_EXTRACT_TEMP_100 does not exist

Please help.

 

[hello99hello]

Based on my last update, I have made some modifications.

However, I am now getting the following error:
SQL*Loader-291: Invalid bind variable LOADDATA in SQL string for column FILE_NUMBER.

 

[ddrillich]

First, the :loaddata.dat variable is wrong.

Please look here

http://www.tek-tips.com/viewthread.cfm?qid=911577&page=1

As you can see, you can only refer to :x and :y, since their data is already extracted.

Regards,
Dan

 

[hello99hello]

Hi Dan,

I have managed to get my control file working, however is it is only loading record #1:
RWP20004100 SM000016721788446C05102A 1875345804015240800++000000000+ 71

This is perfectly all right. Actually I prefer it this way.

All I wanna do now is to create a second and third temporary tables, which I have done. Now, my question to u is can I insert a where clause in control file which would have the second record:


RWP20004200 00000014768200+S2 0050102009583405833652+ 005258403840101

And third control file which would house the third record:

RWP2000430000+000000000+000000000+000000000+0000055834500000000+00RWPX99

Notice that the 9th digit of each record begins differently. That is, can I write a where clause in the following control file?:

LOAD DATA
TRUNCATE
INTO TABLE ADP_EXTRACTS_TEMP_101
TRAILING NULLCOLS
(
FILE_NUMBER POSITION(6:11) ,
RECORD_CODE POSITION(12:14) ,
DATA_CONTROL_1 POSITION(56:56)
)
where FILE_NUMBER "substrloaddata.dat, 1,11 = RWP20004101)

 

[ddrillich]

Here is a control file from an O’Reilly book:

LOAD DATA
INFILE 'keweenaw_county_fixed.dat'
BADFILE 'keweenaw_county_fixed.bad'
DISCARDFILE 'keweenaw_county_fixed.dsc'
INFILE 'alger_county_fixed.dat'
BADFILE 'alger_county_fixed.bad'
DISCARDFILE 'alger_county_fixed.dsc'
APPEND INTO TABLE school
WHEN (feature_type='school')
(
state POSITION(1) CHAR(2),
school_name POSITION(4) CHAR(50),
feature_type FILLER POSITION(55) CHAR(9),
county POSITION(65) CHAR(15)
)
INTO TABLE airport
WHEN (feature_type='airport')
(
state POSITION(1) CHAR(2),
airport_name POSITION(4) CHAR(50),
feature_type FILLER POSITION(55) CHAR(9),
county POSITION(65) CHAR(15)
)

It seems to me that you can use only one control file to load the data into the three tables. You can also use three control files and pass through the data three times.

Try please to apply the when clause. Something like:
When (FILE_NUMBER = '....')

Regards,
Dan

 

[hello99hello]

I have got it, thanks a lot