-
sqlldr in between date
Hi,
I have a huge text file and it loaded so long and lack of the tablespace, it couldn't finish loading.
Is there a way, I can load my text file into Oracle table using where clause in between the date I want to load?
I couldn't find the right syntax to load can you all help? Thanks.
Mine controlfile is:
Load DATA
INFILE './cat_data.txt'
INTO TABLE tm_data
when (datetime between to_date('20070116','yyyymmdd') and to_date('20070122','yyyymmdd'))
fields terminated by "|"
(id, localid, facility, datetime DATE "yyyy-mm-dd hh24:mi:ss", code, cid, acid, type, alt, oldsector, newsector, depart, arrapt, heading, beacon, lat, lon, rules)
-
As far as I remember SQLLoader WHEN clause only accept = and <> conditions... you might want to research "external tables" if you want to process your file using a > or < condition
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I have tried that and still didn't work
LOAD DATA
INFILE './catherine_data.txt'
INTO TABLE catherine_data
WHEN (to_date(datetime,"yyyy-mm-dd") > to_date("2007-01-15", "yyyy-mm-dd'" AND to_date(datetime,"yyyy-mm-dd") < to_date("2007-01-22","yyyy-mm-dd"))
fields terminated by "|"
(id, localid, facility, datetime DATE "YYYY-MM-DD HH24:MI:SS", code, cid, acid, type, alt, oldsector, newsector, depapt, arrapt, heading, beacon, lat, lon, rules)
syntax error at line 4
Expecting = or "<>" found "("
WHEN (to_date(datetime,"yyyy-mm-dd") > to_date("2007-01-15", "yyyy-mmdd"
-
Sorry for not been clear.
You cannot use conditions other than equality (=) and inequality (<>) in a SQLLoader WHEN clause.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I tried
LOAD DATA
INFILE './catherine_data.txt'
INTO TABLE catherine_data
WHEN datetime = '2007-01-16'
fields terminated by "|"
(id, localid, facility, datetime DATE "YYYY-MM-DD HH24:MI:SS", code, cid, acid, type, alt, oldsector, newsector, depapt, arrapt, heading, beacon, lat, lon, rules)
and run the sqlldr... it seems like working but it bombs out and nothing load no error message
Last edited by bvo; 03-29-2007 at 10:09 AM.
-
Check your log looking for rejected rows...
or,
if you want to capture whatever doesn't meet your criteria add a second INTO TABLE bad_catherine_data clause followed by WHEN datetime <> '2007-01016"
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I have tried and nothing wrong... and no error... still not loading. I just load the whole data into the table then it's ok. Still didn't figure out how to load data range
-
You cannot load a range of data, you can ask for "equal to" or "not equal to" there is not "greather than" or "less than" in the SQLLOADER world ;-)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I did put = and it runs for awhile and get back to the prompt sign.
I checked the table and it's empty.
-
It means that the data doesn't meet the criteria you've set:
WHEN datetime = '2007-01016'
Is that a valid date format for your data? It doesn't look right to me...
Assistance is Futile...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|