Sure. Thanks for your interest. The data I want to insert goes into a varchar2(512) column (location_desc), and is a pre-formatted XML location description for an address. It looks something like this:

<NAM>COMPANY</NAM><HNO></HNO><HNS>SW_</HNS><PRD></PRD><STN>PIKE RD & B
+UCK RD</STN><POD></POD><MCN>LOWER SOUTHAMPTON</MCN><COI>MONTGOMERY</C
+OI><STA>PA</STA><ESN>00000</ESN>
[download]

Note the ampersand in the <STN> tag.

And what about something similar to

my @pieces = ('<STN>PIKE RD & BUCK RD</STN>',
              "''' &amp;",
              'another mess');
my $sql = 'insert into mytable (location_desc) values (?);';
my $sth = $dbh->prepare($sql);
foreach (@pieces) {
  $sth->execute($_);
}
[download]

I am not absolutely sure that Oracle DBD can bind variables, but if it can, you, imho, don't have to escape nothing...

I wrote the code by hand, errors can happen :)

It's been a while since I've used Oracle as a backend, but I've never had problems with ampersands through DBI -- the problem only came up when I tried running it through SQL*Plus -- I'm fairly certain that the ampersand issue is a function of SQL*Plus, and not a function of Oracle Database.

Are you using placeholders for the insert/update/whatever? Normally, it's much easier to deal with strings through placeholders, as DBD::Oracle will handle whatever escaping needs to be done.

I believe that 'set define' and 'set escape' are SQL*Plus commands, and as you're not going through SQL*Plus, Oracle Database doesn't know how to handle them. (I would assume 'set editfile' and 'set sqlprompt' would choke as well)