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?
>>
>>
>
>
>
