OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader [message #167243]

Wed, 12 April 2006 01:28

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

Report message to a moderator

Re: SQL Loader [message #167362 is a reply to message #167243]

Wed, 12 April 2006 11:48

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

The problem is that year is an Oracle keyword. It would be better if you could name your column something else. Alternatively, you can enclose it within double quotes, so that SQL*Loader will recognize that it is a column name and not a keyword. You will also need to put it in upper case and you will need to do this everywhere that it is used within the control file. You may find that you encounter the same problem with that column name in some of your sql and pl/sql code.

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)

Report message to a moderator

Re: SQL Loader [message #167482 is a reply to message #167243]

Thu, 13 April 2006 05:07

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)

Report message to a moderator

Re: SQL Loader [message #167491 is a reply to message #167482]

Thu, 13 April 2006 06:24

srhl
Messages: 24
Registered: December 2005

Junior Member

give
year='1997'

or

year=1997

Report message to a moderator

Re: SQL Loader [message #167565 is a reply to message #167482]

Thu, 13 April 2006 21:22

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

You are missing a space between log and discard.

Report message to a moderator

Re: SQL Loader [message #167566 is a reply to message #167491]

Thu, 13 April 2006 21:36

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>

Report message to a moderator

Previous Topic:	IMP.EXE and EXP.EXE
Next Topic:	How to avoid ORA-01438 while using SQL Loader(urgent)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 19:30:53 CDT 2024