This was exactly what I needed. For those of you who might be inclined to use ORA_CHAR instead of SQL_CHAR, that won't work. DBD::Oracle complains and treats the data as SQL_VARCHAR -- at least that was the case for my application.
Thanks to everyone for the excellent feedback and assistance. -dpf- ----------------------- David P. Fannin Database Administrator [EMAIL PROTECTED] UM-Rolla Computing and Information Services FAX (573) 341-4216 URL http://www.umr.edu/~dpf PHONE (573) 341-4841 ----------------------- -----Original Message----- From: Stephen Clouse [mailto:[EMAIL PROTECTED]] Sent: Friday, December 07, 2001 1:17 PM To: Fannin, David P. Cc: '[EMAIL PROTECTED]' Subject: Re: DBD::Oracle -- bind variables treat string of spaces as NULL -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, Dec 07, 2001 at 12:52:35PM -0600, Fannin, David P. wrote: > I've got an application where I need to use bind variables to populate many > tables of a reporting server nightly. I'm using DBD::Oracle to access the > target server. If I use $sth->bind_param ($num, ' ') Oracle treats the > string of spaces as a NULL in my insert statement. Since NULLS aren't > allowed but spaces are, I really need a solution. At this point, the only > that does work is going through the following loop: This tip courtesy of Tim Bunce: use DBI qw(:sql_types); $sth->bind_param(1, $value, SQL_CHAR); The problem is that OCI "smartly" strips trailing blanks off a bind value of type VARCHAR2. Explicitly casting it to a CHAR datatype avoids this "intelligence". - -- Stephen Clouse <[EMAIL PROTECTED]> Senior Programmer, IQ Coordinator Project Lead The IQ Group, Inc. <http://www.theiqgroup.com/> -----BEGIN PGP SIGNATURE----- Version: PGP 6.5.8 iQA/AwUBPBEVtAOGqGs0PadnEQIRugCg+DZy1g0aY3hAjV1vMHfB4UTRnW8AoNMM bnSTMkwskWI9/WSywYlE39ki =EuTz -----END PGP SIGNATURE-----
