default binding of ? parms in Oracle

[Jim Lambert]

Hi,

I'm running into a problem with the 'default' binding
of char columns with Oracle.

I get no results with:
    # example #1
    $parm = '01634';
    $sth = $dbh->prepare('select char_column from table where table.char_column = ?');
    $sth->execute($parm);

but this works fine:
    # example #2
    $parm = '01634';
    $sth = $dbh->prepare('select char_column from table where table.char_column like 
?')
    $sth->execute("$parm%")

If the char_column is a varchar, both queries work great. 

Is there any way to make example #1 work?  Is there some Oracle setting that needs
to be changed?

BTW, I have used code like example #1 extensively on one oracle instance and it
works great.  Now, I'm on a new contract and new oracle instance and it doesn't work?

Any help would be appreciated,

-Jim


P.S.  more example code of the problem follows
#----------------------------------------------
use DBI qw(:sql_types);

use DBD::Oracle;
my $parm = {ChopBlanks=>1,
            AutoCommit=>0,
            LongReadLen=>2**18-2,
            RaiseError=>1,
            PrintError=>0,
        };

$dbi = DBI->connect($connectionsting, $usr, $pass, $parm);

$parm = '01634';

$query = <<THIS;
select code_type, primary_procedure_cd from procedure 
where primary_procedure_cd  = ?
THIS

$sth = $dbi->prepare($query);
$sth->bind_param( 1, $parm, SQL_CHAR);
$sth->execute();

print "Bound as SQL_CHAR\n";
print "Query: $query" . "Parm: $parm\n" . "Results: ";
map { print "[", join ('|', @{$_}), "]"; } @{$sth->fetchall_arrayref};
print "\n*****************\n";

$sth = $dbi->prepare($query);
$sth->bind_param( 1, $parm, SQL_VARCHAR);
$sth->execute();

print "Bound as SQL_VARCHAR\n";
print "Query: $query" . "Parm: $parm\n" . "Results: ";
map { print "[", join ('|', @{$_}), "]"; } @{$sth->fetchall_arrayref};
print "\n*****************\n";


$sth = $dbi->prepare($query);
$sth->execute($parm);

print "No bind or like\n";
print "Query: $query" . "Parm: $parm" . "Results: ";
map { print "[", join ('|', @{$_}), "]\n"; } @{$sth->fetchall_arrayref};
print "\n*****************\n";


$query = <<THIS;
select code_type, primary_procedure_cd from procedure 
where primary_procedure_cd like ?
THIS

$sth = $dbi->prepare($query);
$sth->execute("$parm%");

print "No bind but like %\n";
print "Query: $query" . "Parm: $parm\n" . "Results: ";
map { print "[", join ('|', @{$_}), "]"; } @{$sth->fetchall_arrayref};
print "\n*****************\n";

$query = <<THIS;
select code_type, primary_procedure_cd from procedure 
where primary_procedure_cd = '$parm'
THIS

$sth = $dbi->prepare($query);
$sth->execute;

print "literal string\n";
print "Query: $query" . "Parm: $parm\n" . "Results: ";
map { print "[", join ('|', @{$_}), "]"; } @{$sth->fetchall_arrayref};
print "\n*****************\n";


-- 
<[EMAIL PROTECTED]> (Replace Z's with E's to reply)

"Camel's weren't designed to smell good. Neither was Perl."
-Larry Wall