This query that will work via sqlplus and even MS Access linked via ODBC but not DBI::Oracle. It returns "0E0" (no rows) but succeeds in executing. I have tried rewriting it with a "LIKE" and countless other possibilities. What is happening here? The important line is the variable it seems because nothing is wrong with the joins. That field is a VARCHAR. The variable is currently this string (where $l is some ID I want to use): 
$myvar="location.location_code = '${l}' "

[download]
my $dbh = DBI->connect('DBI:Oracle:','user', 'pass', { RaiseError => 1
+ }) or die "Could not connect to Oracle backend: $DBI::errstr";
        
        my $query = 
        "select bib_mfhd.bib_id 
        from mfhd_master, bib_mfhd, bib_location, location
        where ( $myvar )
        and bib_mfhd.mfhd_id = mfhd_master.mfhd_id
        and bib_location.location_id = location.location_id
        and bib_mfhd.bib_id = bib_location.bib_id
        and mfhd_master.suppress_in_opac='N'
        and display_call_no is not null
        order by bib_id asc";
        
        print "$query\n" if ($ARGV[0] eq "-d");
        my $sth = $dbh->prepare($query) || die "Failed preparing the q
+uery. WTF?";
        
        my $retcode = $sth->execute;

[download]
$retcode is always 0E0. It should be >1200 rows. $query prints out fine, and I cut and pasted that printed value into sqlplus to make sure I was using the same query. What am I missing?

In reply to Query works in sqlplus but not DBI - Solved by shadowfae

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.