On Fri, Mar 01, 2002 at 12:12:28PM +0100, Peter J. Holzer wrote: > 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
I'll fix that. > its numerical value (5) seems to work only against Oracle 8.1 databases, > not Oracle 8.0). Odd. > (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). But can they contain embedded nul bytes? > Anybody knows what the Oracle engineers have been smoking when they > designed this?) :) Tim.
