in reply to Bind param error

If $sth1 is undefined, that means that your prepare statement failed, which is usually attributable to an SQL bug. Try modifying your prepare statement to look like

$sth1=$Dbh->prepare(...) or die "Prepare failed: $DBI::errstr\n";

I also usually try to define my SQL strings independently of the prepare statement to make the code more obvious. An example from some of my code, using a heredoc: 

    my $sql = <<EOSQL;
    
INSERT INTO files (filename, content)
VALUES (?,?)

EOSQL

    my $query = $oracle->prepare($sql)
        or die "Insert prepare failed: $DBI::errstr";
    $query->bind_param(1,$filename)
        or die "Insert bind_param failed: $DBI::errstr";
    $query->bind_param(2,$content,{ora_type   =>  ORA_BLOB})
        or die "Insert bind_param failed: $DBI::errstr";
    $query->execute
        or die "Insert execute failed: $DBI::errstr";

[download]

You can also accomplish something similar using the PrintError or RaiseError flags at connect; see DBI for details. 

    my $oracle = DBI->connect(  @dbi_path,
                    {
                        PrintError => 0,
                        RaiseError => 0,
                        AutoCommit => 0
                    }
                )
        or die "Database connect to $db_name failed:" . $DBI::errstr;

[download]

Replies are listed 'Best First'.
Re^2: Bind param error
by doug145 (Initiate) on Jun 30, 2010 at 20:21 UTC
    Kenneth, The prepare statenment in this script has been running fine for over a year now. I just added an IF....ELSE to it. I'm still not clear what I need to do here.
[reply]
      Assuming you added the or die clause I suggested, the script will die on the prepare. I know this because the next statement says the result was undefined. It will output $DBI::errstr - this should either provide you with the diagnostics to debug your SQL or will not make any sense, in which case the bug is in the Perl code. So, run the script again with the followed subbed for your prepare statement, and post the error message that results if it does not give you the necessary guidance: 

              $sth1=$Dbh->prepare("
             select a.contracting_office_text||h.piin as CONTRACT_NO,
                      substr(h.dodaac, 4, 3)||h.piin||h.call_no as CAL
+L_NO,
                      b.cage_code as CAGE_CD,
                      b.cec_code as DUNNS_PLUS4,
                      vo.check_no,
                      h.dov_no as VOUCHER_NO,
                      h.invoice_no,
                      vo.eft_trace as EFT_NO,
                      to_char(h.dovi, 'YYYYMMDD') as INVOICE_ISSUE_DT,
                      to_char(h.dov_date, 'YYYYMMDD') as PAYMENT_DT,
                      h.invoice_amt,
                      h.dov_amt as CHECK_EFT_AMT,
                      decode(greatest(discount_due_date, h.dov_date), 
+discount_due_date, h.discount_amt,0) as DISCOUNT_AMT,
                      h.penalty_amt as INTEREST_AMT,
                      decode(h.status, 90, 1, 60, 6, 2) as PAY_STATUs,
                      to_char(h.doiar, 'YYYYMMDD') as INVOICE_RECV_DT,
                      to_char(h.doras, 'YYYYMMDD') as MERCH_ACCEPT_DT,
                      s.fao_dssn as PAYING_DSSN,
                      (h.invoice_amt+h.adj_invoice_amt+h.adj_invoice_a
+mt_2+h.adj_invoice_amt_3+h.adj_invoice_amt_4+h.adj_invoice_amt_5+h.ad
+j_i
nvoice_amt_6) as GROSS_INV_AMT,
                      to_char(h.dov_date, 'YYYYMMDD') as DUE_DT,
                      f.routing_no as RECEIVING_BANK_ID,
                      h.adj_invoice_amt as ADJUSTMENT_AMOUNT_1,
                      h.reason_code_i as ADJUSTMENT_REASON_1,
                      decode(h.edi_disc_amt,
                             0,h.adj_invoice_amt_2 + h.adj_invoice_amt
+_3 + h.adj_invoice_amt_4 + h.adj_invoice_amt_5 + h.adj_invoice_amt_6,
                             h.edi_disc_amt) as ADJUSTMENT_AMOUNT_2,
                      decode(h.edi_disc_amt,
                             0,h.reason_code_i_2,
                             'SWELL') as ADJUSTMENT_REASON_2,
                      f.account_no as ACCOUNT_NUMBER,
                      h.currency_code,
                      h.address_line_1 as ADDRESS_1,
                      h.address_line_2 as ADDRESS_2,
                      h.city|| ',' ||h.state_cd||' '||h.zip_code as AD
+DRESS_3,
                      h.company_name as PAYEE_NAME,
                      e.tin,
                      h.vendor_code,
                      h.net_due_date as NET_DUE_DATE, #Added for Revis
+ion 1.6
                      h.status as STATUS #Added for Revision 1.6
            from historydb h, voucher vo, cdb c, cont_office a, vdb b,
+ eft f, vcodedb e, svcuniqdb s
          where h.dov_no = vo.dov_no
              and h.dov_date = vo.dov_date
              and h.piin = c.piin
              and c.status = 20
              and c.contracting_office_id = a.contracting_office_id
              and c.mail_to_code = b.address_code
              and c.vendor_code = b.vendor_code
              and c.vendor_code = f.vendor_code (+)
              and c.eft_addr_code = f.eft_addr_code (+)
              and f.status (+) = 40
              and h.vendor_code = e.vendor_code
              and h.dov_date = ?
              ") 
        or die "Prepare failed: $DBI::errstr";

      [download]
[reply]
[d/l]
[select]
        Kenneth, I added  or die "Prepare failed: $DBI::errstr"; to the end of the statement and I get this error now when I try to run it: Prepare failed: ORA-00911: invalid character (DBD ERROR: OCIStmtExecute/Describe) at /home/mundyda/extract.pl line 143. I do not see an invalid character though. Your thoughts? Doug
[reply]
[d/l]

In Section Seekers of Perl Wisdom