on Oracle 7.3.4.0.1: SQL> @varchar2_test.sql
Table created. PL/SQL procedure successfully completed. 1 row created. Commit complete. COL1 ---------------------- "123 " Michael A Chase wrote: > > It's hard to see what the standard would be. Tim changed the behavior > somewhen around DBD::Oracle 0.90 to allow any character (including NUL) to > be passed through a placeholder. The least surprise would probably be > caused by going back to the old way; anyone who's trying to insert NUL into > a database column including RAWs and BLOBs needs to be real sure about what > they are doing. > > All the SQL*Plus examples I've seen so far in this thread were using text > literals. I don't have access to an Oracle database right now so I can't > test it myself, but I'd be interested to see what the result of this is: > > REM Insert trailing spaces via bind variable > CREATE TABLE tab1 ( col1 VARCHAR2(20) ); > VARIABLE b1 VARCHAR2(20) > EXECUTE :b1 := '123 ' > INSERT INTO tab1 ( col1 ) VALUES ( :b1 ); > COMMIT; > SELECT '"' || col1 || '"' col1 FROM tab1; > > -- > Mac :}) > ** I normally forward private questions to the appropriate mail list. ** > Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html > Give a hobbit a fish and he eats fish for a day. > Give a hobbit a ring and he eats fish for an age. > ----- Original Message ----- > From: "Sterin, Ilya" <[EMAIL PROTECTED]> > To: "'Peter J. Holzer '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Thursday, February 28, 2002 17:32 > Subject: RE: Whitespace being truncated with Oracle > > > Peter, I understand, but silly or not silly, if it's the standard, I'd > > rather have it followed than to fullfill someones request based on their > > current need. I totally agree and you have a good argument, but the > > standard is the standard, and we'll leave not following it up to the > vendors > > like M$ and Oracle. > > > > -----Original Message----- > > From: Peter J. Holzer > > To: '[EMAIL PROTECTED] ' > > Sent: 2/28/02 3:11 PM > > Subject: Re: Whitespace being truncated with Oracle > > > > On 2002-02-28 13:38:23 -0800, Michael Peppler wrote: > > > Jeff Hunter writes: > > > > I agree, it should be fixed. > > > > > > > > Peter J. Holzer wrote: > > > > > > > > >I don't think the current behaviour[1] is correct. In perl, > > strings can > > > > >have trailing spaces: "test" and "test " compare as not equal. > > > > >In Oracle varchar2 can store strailing spaces: If I store 'test ' > > in a > > > > >varchar2 column, I get back 'test ' and not 'test' or 'test > > '. > > > > > > Are you *sure* that you get 'test ' back??? > > > > dialog:~ 22:56 103% sqlplus hjp@dv > > > > SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 28 22:56:38 2002 > > > > (c) Copyright 2000 Oracle Corporation. All rights reserved. > > > > Enter password: > > > > Connected to: > > Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production > > With the Partitioning option > > JServer Release 8.1.7.2.0 - Production > > > > SQL> drop table foo; > > > > Table dropped. > > > > SQL> create table foo (t varchar2(40)); > > > > Table created. > > > > SQL> insert into foo(t) values('test '); > > > > 1 row created. > > > > SQL> select t || 'x' from foo; > > > > T||'X' > > ----------------------------------------- > > test x > > > > SQL> > > > > dialog:~ 23:00 106% ./listtable.pl dv hjp <censored> foo | od -c > > 0000000 T \n t e s t \n > > 0000012 > > dialog:~ 23:00 107% cat listtable.pl > > #!/usr/bin/perl -w > > use strict; > > use DBI; > > > > my @driver_names = DBI->available_drivers; > > > > my $dbh = DBI->connect("dbi:Oracle:${ARGV[0]}", "${ARGV[1]}", > > "${ARGV[2]}"); > > > > my $sth = $dbh->prepare("select * from " . $ARGV[3]); > > > > my $rv = $sth->execute; > > > > print join('|', (@{$sth->{NAME}})), "\n"; > > > > while (my @ary = $sth->fetchrow_array) { > > print join('|', @ary), "\n"; > > } > > > > > > > I'm not be an Oracle specialist, but I know that trailing spaces in > > > varchar() columns are normally removed on insert. This is definitely > > > the case for Sybase - irrespective of the client that is used to > > > access the data. > > > > At least sqlplus doesn't remove spaces on insert, and a select returns > > them from both sqlplus and perl. > > > > > I also seem to recall that this behaviour is a SQL > > > standard. > > > > Quite possible, as mysql seems to behave the same. It's still silly. -- Dr. Frank Ullrich, Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538
