sqlloader syntax error on command line

Question

I can't figure out where is the syntax error in this case trying to execute the sqlloader by command line. It seems to be ok.

Command:

sqlldr myuser/mypass@myhost:1521/orcl CONTROL=tbx.ctl LOG=C:\path\to\tbx.log BAD=C:\path\to\tbx.bad skip=0

CTL file:

load data 
infile 'C:\path\to\tbx.csv'
into table TBX
fields terminated by ';'
optionally enclosed by '"' AND '"'
( x,
  xx,
  xxx,
  xxxx,
  xxxxx,
  xxxxxx,
  xxxxxxx,
  xxxxxxxx,
  xxxxxxxxx
)

CSV file:

"724098100357859";"";"";"";"";"";"";""
"724098100358417";"";"";"";"";"";"";""
...

ERROR:

SQL*Loader-100: Syntax error on command-line

Table structure:

CREATE TABLE TBX
(
"x" VARCHAR2(20 BYTE), 
"xx" VARCHAR2(80 BYTE), 
"xxx" VARCHAR2(80 BYTE), 
"xxxx" VARCHAR2(80 BYTE), 
 "xxxxx" VARCHAR2(60 BYTE), 
 "xxxxxx" VARCHAR2(60 BYTE), 
 "xxxxxxx" VARCHAR2(60 BYTE), 
 "xxxxxxxx" VARCHAR2(60 BYTE), 
 "xxxxxxxxx" VARCHAR2(80 BYTE)
 )

Does your real `\path\to` value have any spaces in it? There may also be a more useful message before the banner. — Alex Poole, Feb 20 '13 at 11:58
and why you don't specify only `orcl` instead of `@myhost:1521/orcl`? If Orcl is defined in tnsnames.ora then you don't need to specify host and port. — Florin Ghita, Feb 20 '13 at 12:13
I could be wrong, but isn't it USERID=blah ? and use tnsnames, so USERID=myuser@mydb/mypwd — tbone, Feb 20 '13 at 12:35
Is your real table created like that? Lower case column names encased in double quotes? If so you have foolishly invoked the wrath of the God of Case Sensitity and you need to use that precise format +including double quotes+ every time you reference the column names. Including SQLLDR control files, which currently you don't. — APC, Feb 20 '13 at 12:44
@AlexPoole C:\Program Files\Zend\Apache2\.. and there aren't more messages, just Syntax error on command-line. — jj-aa, Feb 20 '13 at 16:03
@FlorinGhita because I just prefer do it like that, and also my concepts with Oracle are the basic. — jj-aa, Feb 20 '13 at 16:08
The spaces in the file path are probably confusing it. (I'd expect to see an LRM-00112 error, so it might be something else too). I don't have a Windows install to try on... I *think* you can just enclose the paths in single quotes, `log='C:\path\to\tbx.log'` etc. Not sure on that platform so not a real answer... — Alex Poole, Feb 20 '13 at 16:20
@AlexPoole Ok, I get a different error: listener does not currently know of service requested in connect descriptor. — jj-aa, Feb 20 '13 at 16:29
That goes back to what Florin said; `orcl` might be a TNS alias, or the SID - but that form of connection needs it to be a service name. You can check what service names are valid on the server with `lsnrctl status`... or use a TNS alias if you already have one defined. — Alex Poole, Feb 20 '13 at 16:41
If the table columns are in UPPERCASE why did you post a DDL statement showing them in "lowercase"? If you want us to help you need to provide accurate information. — APC, Feb 20 '13 at 21:24

score 3 · Accepted Answer · answered Feb 20 '13 at 17:10

If your paths have spaces in them then SQL*Loader will see a path as more than one argument, usually generating an LRM-00112 error. You haven't shown that but from comments that does seem to be the issue. You need to enclose the paths in quotes:

sqlldr myuser/mypass@myhost:1521/orcl CONTROL='tbx.ctl' LOG='C:\path\with spaces\tbx.log' BAD='C:\path\with spaces\tbx.bad' skip=0

Off-topic from the original question, but picking up from a comment... When you use @myhost:1521/orcl as your connect string, you're using an easy connection identifier (link is for SQL*Plus, but the same applies here). The final element of that is the service name for the database, which may not be the same as the SID - it could be orcl.example.com, for instance. On the database server you can run lsnrctl status or lsnrctl services to see what service names are valid. If you already have a working tnsnames.ora, though, you can use the TNS alias instead, e.g. sqlldr myusr/mypass@orcl.

sqlloader syntax error on command line

1 Answers1