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.
