Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #167243] |
Wed, 12 April 2006 01:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rvenkatesh
Messages: 3 Registered: December 2005
|
Junior Member |
|
|
Dear All,
I am trying to fetch the transaction of when year=1997. But, I am unable to load into table player_statistics
<STATS.CTL>
load data
infile 'd:\stat.txt' append into table player_statistics
when year="1997"
fields terminated by ',' optionally enclosed by '"' (player_id, year, batting_average, home_runs, stolen_bases)
<stat.txt>
0001,1996,320,10,4
0001,1997,330,12,7
0002,1997,230,0,3
0003,1995,110,3,0
0003,1996,186,6,3
0003,1997,205,12,7
0004,1997,313,33,9
0005,1997,330,40,35
0006,1995,280,5,12
0007,1996,920,9,20
0008,1998,12,1,20
While SQLLDR cmd/Username/Pwd
control=d:\stats.ctl log=d:\stats.log bad=d:\stats.bad discard=d:\stats.dsc
If I remove when year="1997" command I got all the year transactions.
Thanks in advance for this solutions
|
|
|
|
Re: SQL Loader [message #167482 is a reply to message #167243] |
Thu, 13 April 2006 05:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rvenkatesh
Messages: 3 Registered: December 2005
|
Junior Member |
|
|
I got the error as :
unable to open (d:\stats.ctl bad=d:\stats.bad log=d:\stats.logdiscard=d:\stats.dsc
ader-553: file not found
ader-509 System Error: The system cannot fine the specified
even I put it as (d:\stats.ctl bad file =d:\stats.bad log file=d:\stats.log discard=d:\stats.dsc)
|
|
|
|
|
Re: SQL Loader [message #167566 is a reply to message #167491] |
Thu, 13 April 2006 21:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
This is a SQL*Loader control file, not a SQL query, so either double quotes or single quotes will work around 1997, but not no quotes. The following is what I used for tetsing:
-- contents of stat.txt:
0001,1996,320,10,4
0001,1997,330,12,7
0002,1997,230,0,3
0003,1995,110,3,0
0003,1996,186,6,3
0003,1997,205,12,7
0004,1997,313,33,9
0005,1997,330,40,35
0006,1995,280,5,12
0007,1996,920,9,20
0008,1998,12,1,20
-- contents of stats.ctl:
load data
infile 'stat.txt' append into table player_statistics
when "YEAR"="1997"
fields terminated by ',' optionally enclosed by '"'
(player_id, "YEAR", batting_average, home_runs, stolen_bases)
-- test:
scott@ORA92> create table player_statistics
2 (player_id number,
3 year number,
4 batting_average number,
5 home_runs number,
6 stolen_bases number)
7 /
Table created.
scott@ORA92> host SQLLDR scott/tiger control=stats.ctl log=stats.log bad=stats.bad discard=stats.dsc
scott@ORA92> select * from player_statistics
2 /
PLAYER_ID YEAR BATTING_AVERAGE HOME_RUNS STOLEN_BASES
---------- ---------- --------------- ---------- ------------
1 1997 330 12 7
2 1997 230 0 3
3 1997 205 12 7
4 1997 313 33 9
5 1997 330 40 35
scott@ORA92>
|
|
|
Goto Forum:
Current Time: Sat Sep 07 06:04:26 CDT 2024
|