Thursday, October 01, 2009

Workaround for SQL*Loader-297/ORA-01756 problem

Upgraded Oracle SQL*Loader (a.k.a. sqlldr) from a 9i version to a 10gR2 version on two computers, with release on one computer and release on the other.

Some ctl files have the following line:

flag "'N'",

When I'd run sqlldr release or the 9i version of sqlldr, the above line caused no problems.

But when I'd run it on release, I'd get this error:

SQL*Loader-297: Invalid syntax or bind variable in SQL string for column FLAG.
ORA-01756: quoted string not properly terminated

I found I was able to get the ctl files to work with release by changing the problematic line to this:

flag "RTRIM('N')",


David said...

You didn't show the specific context of the one line that was throwing the error but check out and it might be the same solution, though if yours works, then great.

flag constant "'N'",


Unknown said...

Hi David,

You are the man! I looked at a lot of different articles on the orafaq site, but unfortunately didn't come across that one.

flag constant "'N'",

doesn't quite do what I want (Oracle sees it as three characters), but this one does the job:

flag constant "N";