>From: Ian Harisay [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, April 19, 2005 12:43 PM
> <snip>
>My perl program appears to be stripping trailing spaces. I
>dynamically build my sql statement with the following function.
>I am using
> Linux Fedora Core 3
> perl 5.8.5
> DBD::Oracle 1.16
> DBI 1.40
> Oracle 9.2.0.1.0
I'm no expert, but I *think* this might be the issue...
for me it occurred on a SELECT rather than an INSERT but
it makes sense to me that it would affect either. For me
the issue was that a SELECT statement returned data when
the actual value was specified in the prepare, but returned
no data when a place holder was used and the value passed at
execution time.
Try:
use DBD::Oracle qw(:ora_types);
... # after connect but before prepare
$dbh->{ora_ph_type} = ORA_CHAR;
The reason is:
CHAR column values are padded with spaces to the length
of the column. When compared with a literal ('PAYMENT'),
the SQL engine automatically adds the padding to the
literal. When compared with a placeholder, the blank
padded semantics are not available unless the placeholder
type is CHAR. Evidently this is not unique to the Perl DBI,
it is Oracle specific and is documented in the Oracle OCI
manual, but the above is how you would deal with it in perl.
HTH,
Paula
