On 2002-02-28 15:58:31 -0800, Rob Bloodgood wrote: > > Now of course Oracle and some other vendors I don't care to mention > > love straying from the path and taking their own approach, but then > > call themselves SQL ## compliant. Oracle has a major trailing space > > problem that was discussed up and down this list many times. Peter, > > just look at the archives. But I think following the standard is > > the way to go. > > Ahem... The VARCHAR2 datatype does NOT appear in the SQL standard. Oracle > knows, and clearly states somewhere in the docs, that its implementation of > the VARCHAR datatype tries to match the standard, and that they like > VARCHAR2 better, so it's in there as well.
In a table, oracle doesn't distiguish between VARCHAR2 and VARCHAR. If
you try to create a VARCHAR column, Oracle creates a VARCHAR2 instead.
As an external data type, the only difference seems to be how the length
is specified (in an extra parameter or in the first 2 bytes of the
string). Both strip trailing spaces. To avoid this you would have to use
CHAR or STRING (ORA_STRING isn't defined by DBD::Oracle, BTW, and using
its numerical value (5) seems to work only against Oracle 8.1 databases,
not Oracle 8.0).
(Personally, I find this perplexing. When I have a CHAR variable, it
probably contains padding spaces - they have no meaning, so when I
insert it into a VARCHAR2 column, I want them stripped. OTOH, when I
have a VARCHAR(2) variable, it doesn't contain trailing spaces unless I
added them myself. And I probably did add them for a good reason, so I
do not want stripped. And PL/SQL seems to work differently from OCI
again (neither char nor varchar2 variables are stripped on insert).
Anybody knows what the Oracle engineers have been smoking when they
designed this?)
hp
--
_ | Peter J. Holzer | My definition of a stupid question is
|_|_) | Sysadmin WSR / LUGA | "a question that if you're embarassed to
| | | [EMAIL PROTECTED] | ask it, you stay stupid."
__/ | http://www.hjp.at/ | -- Tim Helck on dbi-users, 2001-07-30
msg09296/pgp00000.pgp
Description: PGP signature
