| SQL LOADER [message #126022] |
Thu, 30 June 2005 10:23  |
pa1sas3
Messages: 29
Registered: June 2005
|
Junior Member |
|
|
Hi all,
I have a '|' separated data file for input.
The first character is D.
I want to ignore this field as there is no corresponding column in the stage table. D is meant only to say that it is a data record. The control file is as below:
LOAD DATA
APPEND
INTO TABLE stage
when (1) = 'D'
fields terminated by '|' optionally enclosed '"'
TRAILING NULLCOLS
(
dummy_col1 filler,
real_col1 char(20),
real_col2 char(200),
real_col3 char(5),
......)
Input file is like:
D|22222|xxxxx|12345|abcde|...
The first filed in the file is to be ignored. So, I used a dummy_col1 in the control file. dummy_col1 does not exist in the table.
i tried
dummy_col1 char(1),
as well. But it shows the following error.
SQL*Loader-350: Syntax error at line 8.
Expecting "," or ")", found "filler".
dummy_col1 filler,
How should i ignore the first field. Is it a special case.
Or does my sql loader does not support filler.
The version is
SQL*Loader: Release 7.3.4.4.0
and oracle version is 9.0.1
Pavan.
|
|
|
|
|
|
| Re: SQL LOADER [message #126031 is a reply to message #126026] |
Thu, 30 June 2005 11:20  |
pa1sas3
Messages: 29
Registered: June 2005
|
Junior Member |
|
|
Hey,
I dont know why they(my company) r using the older version of sqlldr. What is the latst version by the way? I just mentioned abt the version thinking it could be the reason for the error. Is it the problem by any chance?
Is my CTL file OK ?????...
Or is there any other work around to this problem?
Ragards,
Pavan
|
|
|
|
|
|
| Re: SQL LOADER [message #126035 is a reply to message #126022] |
Thu, 30 June 2005 11:27 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
If all of your data is in one database version, then by all means, use the sqlldr that comes with that version. It doesn't cost anything extra.
|
|
|
|
| Re: SQL LOADER [message #126063 is a reply to message #126035] |
Thu, 30 June 2005 14:04 |
pa1sas3
Messages: 29
Registered: June 2005
|
Junior Member |
|
|
Hey,
It was a problem with the version of sqlldr as expected. I pointed to 9.2 in the script and it works fine.
Thanks for the help.
Pavan
|
|
|
|
| Re: SQL LOADER [message #126861 is a reply to message #126022] |
Thu, 07 July 2005 00:34 |
chris018
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
i am a little new to oracle
i'm having the same problem that pavan had eventhough the version is not very very old
Oracle8 Enterprise Edition Release 8.0.5.0.0
SQL Loader: Release 8.0.5.0.0
below are the files:
1)
i created a table named 'christine1' in database with these fields:
cid
dept
start_date
end_date
state
2)
UploadToChristine1.csv
cid middle dept start_date end_date state
17 mid hr 19-Mar-05 25-Mar-05 passive
3)
uploadtochristine2.ctl
LOAD DATA
INFILE 'D:\uploadtochristine1.csv'
APPEND
INTO TABLE christine1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(cid,
middle filler,
dept,
start_date INTEGER EXTERNAL,
end_date INTEGER EXTERNAL,
state)
3)
i typed in the cmd as:
sqlldr80 ikhlas/jer123@ikhlastest control=D:\uploadtochristine2.ctl
what i want is, when i upload the .csv file to database, i don't want the colomn 'middle' to be uploaded. where have i went wrong?
|
|
|
|
|
|
|
|
| Re: SQL LOADER [message #126956 is a reply to message #126950] |
Thu, 07 July 2005 09:17 |
chris018
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
does that means that there is no way i can do the filler function with that version is it?
sigh, i'm working in a big company, i can't ask them get the new version of oracle, ..... 100s of people there are using this old oracle.
Christine.
|
|
|
|
| Re: SQL LOADER [message #126983 is a reply to message #126022] |
Thu, 07 July 2005 13:22 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Well, I suppose you could load the data into a temp table, that has the bogus fields you want to eventually skip in the long run. Just load them anyway into your temp table. Then have your real table select out only the columns you really want from the temp table.
Your "old version" issues are just going to get worse and worse. Is it worth it to your company to pay for your time spent doing things the hard and long and workaround way when there would be so much easier methods if you were on a reasonably current version? How about paying for the execution performance of the system itself, which gets better and better, meaning less hardware to run the same load, and faster response times for your customers.
You pay for upgrading either way. Either the cost to do it, or the cost to not do it. But if you do it, you pay for it once. But if you don't do it, you pay for it day in and day out.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
