On 11/25/2004 12:28 AM, Steinhauer, Frank (CAM) said:
I found it in the docs- but to tell the truth, I wouldn't understand it
without my knowledge now. It REALLY should be stated clearly, that the default CHANGES THE PARAMETERS
- btw, I still don't understand why. I think this is really an important
issue, that parameters are AUTOMATICALLY changed, and that should be stated
as clearly as possible. There should be a warning in DBI too, because that's
the first place to look for most people.
I'm not sure what you mean by CHANGES THE PARAMETERS. What happens is that Oracle OCI _interprets_ VARCHAR values in a way that seems odd to most people. At some point someone thought stripping trailing blanks from VARCHAR values would be a good idea. It probably was decided when VARCHAR was introduced and they had lots of CHAR columns with those pesky trailing blanks.
I answered basically the same questions yesterday and the questioner had found an answer from me in 2002. Since this has been coming up for a long time and still comes up fairly often, some improvement in the documentation is in order. If you have any verbiage that would make it clearer to you, please suggest it.
The information doesn't belong in the DBI docs since the problem only applies to DBD::Oracle.
Possible addition to DBD::Oracle documentation (after Placeholder Binding Attributes):
=====
=head2 Trailing Spaces
The Oracle strips trailing spaces from VARCHAR placeholder values and uses Nonpadded Comparison Semantics with the result. This causes trouble if the spaces are needed for comparison with a CHAR value or to prevent the value from becoming '' which Oracle treats as NULL. Look for Blank-padded Comparison Semantics and Nonpadded Comparison Semantics in Oracle's SQL Reference or Server SQL Reference for more details.
Please remember that using spaces as a value or at the end of a value makes visually distinguishing values with different numbers of spaces difficult and should be avoided.
To preserve trailing spaces in placeholder values, either change the default placeholder type with L</ora_ph_type> or the placeholder type for a particular call to L<DBI/bind> or L<DBI/bind_param_inout> with L</ora_type> or C<TYPE>. Using L<ORA_CHAR> with L<ora_type> or C<SQL_CHAR> with C<TYPE> allows the placeholder to be used with Padded Comparison Semantics if the value it is being compared to is a CHAR, NCHAR, or literal. =====
-- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Cthulhu in 2004. Don't settle for the lesser evil.
