Just explained, does the other Perl version have a different DBD::Oracle
installed (lower version). Probably means that it was fixed in the higher
version. Again, I don't see what the problem is, since the behavior is
correct. You are yet to get back with us with the result of the examples
that the nice people on this list were able to provide, yet you keep trying
to prove something is wrong, and we tell you that it is the correct
behavior.
Ilya
-----Original Message-----
From: Jeff Hunter
To: Jeff Hunter
Cc: Sterin, Ilya; '[EMAIL PROTECTED] '
Sent: 2/28/02 11:24 AM
Subject: Re: Whitespace being truncated with Oracle
Let me see if I can explain with an example:
oracle8@dev42 $ sqlplus [EMAIL PROTECTED]
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 28 13:02:39 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> desc xyz
Name Null? Type
----------------------------------------- --------
----------------------------
X NUMBER(10)
Y VARCHAR2(10)
Z VARCHAR2(10)
SQL> select * from xyz;
no rows selected
SQL> quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 -
Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
oracle8@dev42 $ test1.pl
Clearing all previous data...done.
Performing 10 bound inserts...done.
Performing 10 updates...done.
oracle8@dev42 $ sqlplus [EMAIL PROTECTED]
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 28 13:02:57 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> @q1.sql
X Y LEN_Y Z LEN_Z
---------- ------------ ---------- ------------ ----------
1 |1 | 6 |1| 1
2 |2 | 6 |2| 1
3 |3 | 6 |3| 1
4 |4 | 6 |4| 1
5 |5 | 6 |5| 1
6 |6 | 6 |6| 1
7 |7 | 6 |7| 1
8 |8 | 6 |8| 1
9 |9 | 6 |9| 1
10 |10 | 7 |10| 2
10 rows selected.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 -
Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
oracle8@dev42 $ exit
#!/usr/local/perl/bin/perl -w
use strict;
use DBI;
my $exitStatus = 0;
my $name = "dev817.us";
my $drh = DBI->install_driver('Oracle');
my $dbh = $drh->connect($name, '/', '') ||
&exitWithError("$DBI::errstr");
&ClearValues;
&Insert;
$dbh->disconnect;
exit($exitStatus);
#---------------------------
sub exitWithError
{
my ($error) = shift;
$exitStatus = 1;
print "Exiting with ERROR: $error\n";
exit($exitStatus);
}
#---------------------------
sub Insert{
my $initial = q{
INSERT INTO xyz (x, y)
VALUES (?, to_char(?) || ' ')
};
my $upd = q{
UPDATE xyz
SET z = ?
WHERE x=?
};
my $qry = q{
SELECT x, y FROM xyz
};
print "Performing 10 bound inserts...";
my $sth = $dbh->prepare($initial);
for (my $i=1; $i<=10; $i++) {
$sth->bind_param(1,$i);
$sth->bind_param(2,$i);
$sth->execute() || &exitWithError("$DBI::errstr");
}
$dbh->commit;
print "done.\n";
print "Performing 10 updates...";
my ($strval,$intval);
$sth=$dbh->prepare($qry) || &exitWithError("$DBI::errstr");
$sth->execute() || &exitWithError("$DBI::errstr");
$sth->bind_col(1,\$intval);
$sth->bind_col(2,\$strval);
my $uph=$dbh->prepare($upd) || &exitWithError("$DBI::errstr");
while ($sth->fetch ) {
$uph->bind_param(1,$strval);
$uph->bind_param(2,$intval);
$uph->execute() || &exitWithError("$DBI::errstr");
};
$dbh->commit;
print "done.\n";
}
#---------------------------
sub ClearValues {
my $SQL = q{
TRUNCATE TABLE xyz
};
print "Clearing all previous data...";
my $sth = $dbh->prepare($SQL);
$sth->execute() || &exitWithError("$DBI::errstr");
print "done.\n";
}
If I run this same program on a different perl version, I get:
oracle8@clr5 $ test1.pl
Clearing all previous data...done.
Performing 10 bound inserts...done.
Performing 10 updates...done.
oracle8@clr5 $ sqlplus [EMAIL PROTECTED]
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 28 13:22:10 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> @q1
X Y LEN_Y Z LEN_Z
---------- ------------ ---------- ------------ ----------
1 |1 | 6 |1 | 6
2 |2 | 6 |2 | 6
3 |3 | 6 |3 | 6
4 |4 | 6 |4 | 6
5 |5 | 6 |5 | 6
6 |6 | 6 |6 | 6
7 |7 | 6 |7 | 6
8 |8 | 6 |8 | 6
9 |9 | 6 |9 | 6
10 |10 | 7 |10 | 7
10 rows selected.
SQL> quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 -
Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
oracle8@clr5 $ exit
Jeff Hunter wrote:
> I failed to mention that this worked with Perl 5.5.2 and an earlier
> version of the DBI/DBD.
>
>
> Sterin, Ilya wrote:
>
>> No, this is not a bug. varchar automatically strips trailing spaces,
so
>> it's an Oracle thing. I would recommend familiarizing yourself with
the
>> ORacle data types. Char is probably what you want to use.
>>
>> Ilya
>>
>> -----Original Message-----
>> From: Jeff Hunter
>> To: [EMAIL PROTECTED]
>> Sent: 2/28/02 9:40 AM
>> Subject: Whitespace being truncated with Oracle
>>
>>
>> I am using perl 5.6.1 on Solaris 2.8. My DBI version is DBI-1.21 and
my
>>
>> DBD version is DBD-Oracle-1.12. My Oracle version is 8.1.7.2 and my
>> Oracle OCI version is 8.1.7.0.0.
>>
>> I have setup a query that retrieves VARCHAR2(10) values from a table
>> into a bound variable. The values in the table are right padded with
>> spaces. When I retrieve the values, I can print them out exactly as
>> they are in the table. However, when I insert them into another
>> table, the spaces at the end of the variable are stripped off. I
>> think the spaces are being truncated in the bind_param method. Is
>> this a bug with
>>
>> the DBD I am using?
>>
>>
>
>
>
