Executing Oracle PL/SQL statements through the DBI

ptum has asked for the wisdom of the Perl Monks concerning the following question:

Update: I can no longer replicate the original problem with inserting strings with ampersands. The prepare/execute approach with bind variables (suggested by [id://pajout] and [id://kwaping]) worked, but then, so did my original insert. I am tentatively concluding that, as [id://Roy Johnson] and [id://jhourcle] suggest, my problem was entirely within SQLPlus, and that the error I saw in my Perl DBI script execution was something else (probably a referential integrity issue). Presumably when I followed up on that original error by pasting the offending statement into SQLPlus, I was fooled by the &var replacement red herring.

Either that or evil database gnomes have been at work during the night, in an unnecessary effort to discredit me.

Thank you for all of your suggestions and help.

=================================

Original post: I have some data which I need to insert into an Oracle table; the data has embedded ampersands in it, which causes Oracle to prompt for a parameter (and my insert to fail). Using SQLPlus, I see that I can either:

"set define off" or

"set escape \" and then escape the ampersand with a preceding backslash.

But I can't seem to get this to work through the DBI. Here's what I have tried so far:

  my $data_source = "dbi::Oracle:$sid";
  my $sqlhandle = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=15
+21", 
               $user, $password, { RaiseError => 1, AutoCommit => 0 })
      or die "Can't connect to $data_source: $DBI::errstr";

  my $func = $sqlhandle->prepare(q{
        BEGIN
            set define off;
        END;
  });
    
  my $result = $func->execute();
[download]

I'm getting an error message much like this one:

DBD::Oracle::st execute failed: ORA-06550: line 3, column 17:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 3, column 13:
PL/SQL: SQL Statement ignored (DBD ERROR: error possibly near <*> indi
+cator at char 31 in '
        BEGIN
            set <*>define off;
        END;
  ') [for Statement "
        BEGIN
            set define off;
        END;
  "] at /blah/blah/Blah.pm line 348, <BLAHFILE> line 104133.
[download]

Any help or suggestions would be greatly appreciated.

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

Comment on Executing Oracle PL/SQL statements through the DBI Select or Download Code

Replies are listed 'Best First'.
Re: Executing Oracle PL/SQL statements through the DBI by Roy Johnson (Monsignor) on Mar 14, 2006 at 16:00 UTC
`&var` replacement and all the set commands are part of SQLPlus, which DBD::Oracle does not access in any way. DBD::Oracle will work as if you've got `set define off`. Also, even in SQLPlus, you'd find that using set in a PL/SQL block (like you show above) won't work. Caution: Contents may have been coded under pressure.	[reply] [d/l] [select]
Re^2: Executing Oracle PL/SQL statements through the DBI by ptum (Priest) on Mar 14, 2006 at 16:47 UTC
I think you're right. I can't replicate the problem I was having yesterday;. Whether I set define on or off in a SQLPlus session, my script using DBI/DBD::Oracle seems to happily insert the data without treating the embedded ampersand in any way special. This is very strange, since I'm pretty sure I was getting an error about that yesterday. (Well, I am becoming less sure every moment.) I tried the BEGIN/END PL/SQL block because a simple `$sqlhandle->do("set define off");` wasn't working. Thanks for helping me to understand the different rules that are in play in SQLPlus vs. through the DBI. No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply] [d/l]
Re: Executing Oracle PL/SQL statements through the DBI by kwaping (Priest) on Mar 14, 2006 at 15:30 UTC
Have you tried using placeholders for your data? --- It's all fine and dandy until someone has to look at the code.	[reply]
Re^2: Executing Oracle PL/SQL statements through the DBI by ptum (Priest) on Mar 14, 2006 at 15:48 UTC
Hmmmm. That seems like a good idea. I don't usually use placeholders and the prepare/execute approach unless I am looking for performance gains on a repeated operation, but in this case it might prevent Oracle from treating the ampersand as a variable reference since it would be already inside a bind variable. I'll try it ... and post an update. It will take me a little while to set it up ... the actual insert is in a SUPER class, but I build the statement lower in the inheritance chain. Thanks. No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply]
Re: Executing Oracle PL/SQL statements through the DBI by pajout (Curate) on Mar 14, 2006 at 15:15 UTC
Could you show the relevant piece of the data?	[reply]
Re^2: Executing Oracle PL/SQL statements through the DBI by ptum (Priest) on Mar 14, 2006 at 15:26 UTC
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	[reply] [d/l]
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]