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 10.2.0.1.0 on one computer and release 10.2.0.4.0 on the other.

Some ctl files have the following line:

flag "'N'",

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

But when I'd run it on release 10.2.0.1.0, 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 10.2.0.1.0 by changing the problematic line to this:

flag "RTRIM('N')",

2 comments:

David said...

You didn't show the specific context of the one line that was throwing the error but check out http://www.orafaq.com/forum/t/79142/2/ and it might be the same solution, though if yours works, then great.

flag constant "'N'",

David

Richard Upton 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";