On 2007-09-14 18:03:15 +0100, Martin Evans wrote: > I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle when > using utf8 and I need to as my data is utf8 in Perl. > > Grossly simplified my code does: > > o select integer_primary_key_field from table > o prepare(select from another_table where field = ?) > o execute($inter_primary_key_value_retrieved_from_select) > This query is vastly more complex than this really > > Even though the field retrieved from the first table is an integer when I > look > at it, Perl has utf8 flag set. When these utf8 encoded integers are then > passed > into the execute for a select on another table the execute takes 0.7s. Now > that > may not sound a lot to you but this query gets runs a lot. If I downgrade the > integer parameter with utf8::downgrade before passing it to execute the > execute > takes 0.01s. > > When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think > Oracle > has decided it cannot use an index on the column. > > I tried binding the parameter as ora_number but that does not help. The only > thing which seems to work is to downgrade the parameter from utf8. > > Any ideas?
This may be the same problem I ran into a few weeks ago. See http://www.mail-archive.com/dbi-users@perl.org/msg30138.html I have a patch for this but I still haven't gotten around to testing it, so even though it's only a few lines I don't want to post it yet. Feel free to contact me off-list if you want to try it. The workaround which I actually use in production code is to set NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have nvarchar2 or nclob columns. Explicitely binding with the correct cs_form also works: $sth->bind_param(1, $name, { ora_csform => SQLCS_IMPLICIT }); hp -- _ | Peter J. Holzer | If I wanted to be "academically correct", |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpKmME1flZrp.pgp
Description: PGP signature
