| SQL Loader (Urgent Need immediate reply) [message #358053] |
Sat, 08 November 2008 00:59  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
I have control file below but executing that control file in unix environment i got the below error
can anyone sort this problem
LOAD DATA
INFILE '/usr/tmp/ashok/Loadtest.csv'
INTO TABLE LOADTEST
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER EXTERNAL,
NAME CHAR(20) "LTRIM(RTRIM(:NAME)),
AGE INTEGER EXTERNAL
)
while executing the above i got below error as
SQL*Loader - 350 : Syntax error at line 1
Expecting keyword LOAD found "id"
my sql file to connect sql loader
sqlldr ashok/ashok@ql
control = /usr/tmp/ashok/Loadtest.csv
[Updated on: Sat, 08 November 2008 01:50] Report message to a moderator |
|
|
|
|
|
| Re: SQL Loader (Urgent Need immediate reply) [message #358105 is a reply to message #358053] |
Sat, 08 November 2008 10:25  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You are missing a closing quote:
NAME CHAR(20) "LTRIM(RTRIM(:NAME)),
it should be:
NAME CHAR(20) "LTRIM(RTRIM(:NAME))",
and you have named your control file the same as your data file. Also, sqlldr is an operating system command, so you should be either running it from an operating system prompt or preceding it with the host command to run it from SQL*Plus. There may be other errors. You should post a complete reproducible problem, including create statement for your table and sample data.
[Updated on: Sat, 08 November 2008 10:31] Report message to a moderator |
|
|
|
| Re: SQL Loader (Urgent Need immediate reply) [message #377226 is a reply to message #358105] |
Mon, 22 December 2008 00:17 |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
I have a .csv file like below.
I will post if any problem in loader in various forum as you mention give the solution for this.
Loadtest.csv
=============
Name,Age
Ashok,26
Maha,25
Raj,28
Total Number of Records-3
[CODE] LOAD DATA
INFILE '/usr/tmp/ashok/Loadtest.csv'
[B]WHEN NAME <> "Total%"[/[/B]CODE]
INTO TABLE LOADTEST
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER EXTERNAL,
NAME CHAR(20) "LTRIM(RTRIM(:NAME)),
AGE INTEGER EXTERNAL
)
In that csv file i have Total Number of records in the last line
i want to skip that record while loading into the file but in my scenorio it is trying to insert the last record into the table but finally failed to insert the last record all other records are inserted successfully into the table but the return value from the loader is not successful (0).
I want it should be sucessful because with this return value i am going to do another functionality in my shell script.
Is my WHEN clause is correct if not give the correct one.
|
|
|
|
|
|
| Re: SQL Loader (Urgent Need immediate reply) [message #377394 is a reply to message #377226] |
Mon, 22 December 2008 13:02 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If the top row that just has the words name and age is actually in your data then you need to use the skip=1 option to skip the first row.
You have an ID field in your control file, but no ID field in your data.
You still haven't fixed the first error that I pointed out of the missing ending quote.
Although the when clause works in 11g, you might need to use positional notation in earlier versions.
There is no way to get it to return 0 when any row has failed a when clause. That is just how it works.
You should at least get everything working besides the problem that you are addressing before posting. Or perhaps the problem is that you are not copying and pasting the actual control file and data as you should. In either case, there is little motivation to respond to such rubbish that shows a lack of effort on your part and labeling it urgent is just irritating.
[Updated on: Mon, 22 December 2008 13:04] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
