Home » RDBMS Server » Server Utilities » SQL Loader (Urgent Need immediate reply)
SQL Loader (Urgent Need immediate reply) [message #358053] Sat, 08 November 2008 00:59 Go to next message
ORAGENASHOK
Messages: 238
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 [message #358055 is a reply to message #358053] Sat, 08 November 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you post this in "JDeveloper, Java & XML" forum?
Are you randomly picking the forum in which you post your question?

Regards
Michel
Re: SQL Loader (Urgent Need immediate reply) [message #358105 is a reply to message #358053] Sat, 08 November 2008 10:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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 #377391 is a reply to message #377226] Mon, 22 December 2008 12:40 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Obviously this was not urgent or in need of an immediate reply.
Re: SQL Loader (Urgent Need immediate reply) [message #377394 is a reply to message #377226] Mon, 22 December 2008 13:02 Go to previous message
Barbara Boehmer
Messages: 8635
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

Previous Topic: Date with Null Problem in SQL Loader
Next Topic: Microsoft Access Data
Goto Forum:
  


Current Time: Fri Dec 09 00:25:12 CST 2016

Total time taken to generate the page: 0.07530 seconds