SQL-Loader and special characters

[Nils Appel]

Hi,

I'm trying to import data that contains a lot of "&" signs and other special
characters with sql-loader.

To be able to insert such data I need to execute the command "set define
off" first. The problem is that it seems that one can't define such a
statement whithin SQL-loader. Also I have no idea how to make the
set-command last for ever (now I need to execute the "set define off"
command everytime I log on to SQL+).

Can anyone help me with this?

Tanks.

[Matt B.]

"Nils Appel" <Nils_...@web.de> wrote in message
news:94c74f$8o8$06$1...@news.t-online.com...

Also try "set scan off" - not sure if that'll help or not but give it a try.

Not sure if this will work but assuming the '&' is defined in the INIT file for
the database, you can also try finding the database's INIT file (probably in
$ORACLE_HOME/dbs) and change the '&' to some other special character, like a
tilde (~) until you are finished and then change it back again. You will need
to bounce the database for each change to take effect.

Or, just change your file to have the & replaced by a tilde and then after you
get the data into the database, change it back again.

-Matt

[Sybrand Bakker]

Sorry to say so, but this response is utter crap.
Did you try it yourself? Of course not.
The & is a special character in sqlplus where I should stress the word
*plus*. As such this is a sqlplus issue *only*, and as sqlplus configuration
is not managed by changing init<sid>.ora, changing init<sid>.ora is not
going to have *any* effect.
'You will need to bounce the database for each change to take effect' this
could easily be interpreted as 'you need to bounce the database for every
individual change' and of course this is nonsense, as all the changes you
make at the same time do reflect in your live parameters when you bounce the
database *once*.
In order to change the define settings you need to create a file called
login.sql in the working directory your run sqlplus from. But then again,
this applies to sqlplus only,
ampersands should load without problem using sqlloader, as the ampersand is
*not* a special character in sqlloader. If you don't believe that please
come with concrete evidence of the contrary.

Regards,

Sybrand Bakker, Oracle DBA

"Matt B." <m...@ds.znet.com> wrote in message
news:t6jopl2...@corp.supernews.com...