Re^2: Executing Oracle PL/SQL statements through the DBI

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.

No good deed goes unpunished. -- (attributed to) Oscar Wilde

Comment on Re^2: Executing Oracle PL/SQL statements through the DBI Download Code

Replies are listed 'Best First'.
Re^3: Executing Oracle PL/SQL statements through the DBI by pajout (Curate) on Mar 14, 2006 at 15:45 UTC
And what about something similar to `my @pieces = ('<STN>PIKE RD & BUCK RD</STN>', "''' &", '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 :)	[reply] [d/l]
Re^3: Executing Oracle PL/SQL statements through the DBI by jhourcle (Prior) on Mar 14, 2006 at 15:46 UTC
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 SQLPlus -- I'm fairly certain that the ampersand issue is a function of SQLPlus, 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 SQLPlus commands, and as you're not going through SQLPlus, Oracle Database doesn't know how to handle them. (I would assume 'set editfile' and 'set sqlprompt' would choke as well)	[reply]