oracle - Dashes Causing SQL Trouble in DBI -

i have sql query clause typically has values including dash stored in database char(10). when explicitly call in following:

$sth = $dbh->prepare("select status_code mytable acc_type = 'a-50c'");

it works , returns 1 row; if following:

my $code = 'a-50c'; $sth = $dbh->prepare("select status_code mytable acc_type = ?"); $sth->execute($code); 

or do:

my $code = 'a-50c'; $sth = $dbh->prepare("select status_code mytable acc_type = ?"); $sth->bind_param(1, $code); $sth->execute(); 

the query completes, no results. suspect has dash being interpretted incorrectly, can't link perl issue have printed $code variable using print "my content: $code\n"; can confirm not being strangely converted. tried including third value bind_param , if specify ora_varchar2, sql_varchar (tried possibilities) still no results. if change long form i.e. { type => sql_varchar } gives me error of

dbi::st=hash<0x232a210>->bind_param(...): attribute parameter 'sql_varchar' not hash ref

lastly, tried single , double quotes in different ways ticks escape values, nothing got me 1 row, 0. ideas? haven't found in documentation or searching. oracle reference.

code error checking:

my $dbh = dbi->connect($dsn, $user, $pw, {printerror => 0, raiseerror => 0})   or die "$dbi::errstr\n";  # $dbh = dbi->connect(); # connect  $code = 'a-50c'; print "my content: $code\n"; $sth = $dbh->prepare( "select count(*) mytable code = ?" )   or die "can't prepare sql statement: $dbi::errstr\n"; $sth->bind_param(1, $code); $sth->execute() or die "can't execute sql statement: $dbi::errstr\n";  $outfile = 'output.txt'; open outfile, '>', $outfile or die "unable open $outfile: $!";  while(my @re = $sth->fetchrow_array) {     print outfile @re,"\n"; }  warn "data fetching terminated error: $dbi::errstr\n"   if $dbi::err;  close outfile;  $sth->finish(); $dbh->disconnect(); 

i ran trace , got back:

-> bind_param dbd::oracle::st (dbi::st=hash(0x22fbcc0)~0x3bcf48 2 'a-50c' hash(0x22fbac8)) thr#3b66c8 dbd_bind_ph(1): bind :p2 <== 'a-50c' (type 0 (default (varchar)), attribs: hash(0x22fbac8)) dbd_rebind_ph_char() (1): bind :p2 <== 'a-50c' (size 5/16/0, ptype 4(varchar), otype 1 ) dbd_rebind_ph_char() (2): bind :p2 <== ''a-50' (size 5/16, otype 1(varchar), indp 0, at_exec 1)     bind :p2 ftype 1 (varchar) dbd_rebind_ph(): bind :p2 <== 'a-50c' (in, not-utf8, csid 178->0->178, ftype 1 (varchar), csform 0(0)->0(0), maxlen 16, maxdata_size 0) 

your problem result of comparing char , varchar data together.

the char data type notorious (and should avoided), because stores data in fixed-length format. should never used holding varying-length data. in case, data stored in acc_type column take 10 characters of storage. when store value length less size of column, a-50c, database implicitly pad string 10 characters, actual value stored becomes a-50c_____ (where _ represents whitespace).

your first query works because when use hard-code literal, oracle automatically right-pad value (a-50c -> a-50c_____). however, in second query use bind variables, you're comparing varchar against char , no auto-padding happen.

as quick fix problem, add right-padding query:

select status_code mytable acc_type = rpad(?, 10) 

a long-term solution avoid using char data type in table definitions , switch varchar2 instead.