Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

DBI problem on AIX/Oracle 10g

by hibbarra (Novice)
on Jul 24, 2007 at 17:25 UTC ( [id://628527] : perlquestion . print w/replies, xml ) Need Help??

hibbarra has asked for the wisdom of the Perl Monks concerning the following question:

Sirs. I have DBI code which connects to a newly installed Oracle 10g database. When I run this code under Sun Solaris it works fine. However when I move the same code to an AIX machine I get the following error.
DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DB +D ERROR: OCIStmtExecute) [for Statement "SELECT count(*) FROM trans_hist WHERE TO_CHAR(sched_dttm,'YY/MM/DD') = ? and dep_id = ? and file_id = ?" with ParamValues: :p1='07/03/07', :p2= +'1', :p3='300'] at QueMgrDaemon.pl line 2318. Can't execute statement: SELECT count(*) FROM trans_hist WHERE TO_CHAR(sched_dttm,'YY/MM/DD') = ? and dep_id = ? and file_id = ? , ORA-01008: not all variables bound (D +BD ERROR: OCIStmtExecute) at QueMgrDaemon.pl line 2318.
I am connecting using host, user, password and sid therefore I am bypassing the client and tnsnames.ora. I have to do this as I could not wait for tnsnames.ora to be edited, been 2 months still waiting.
{ my $sth; sub CheckTransmissionHist { my ( $FileId, $DepartmentID, $ScheduleDttm) = @_; $ScheduleDttm = '07/03/07'; my $ltqs_dbh = &ConnectToDatabase(); my $SelStatement = "SELECT count(*) FROM trans_hist WHERE TO_CHAR(sched_dttm,'YY/MM/DD') = ? and dep_id = ? and file_id = ?"; unless( defined($sth)) { $sth = $ltqs_dbh->prepare($SelStatement) or die "Can't prepare statement:$SelStatement , $DBI::errst +r"; } $sth->bind_param( 1, $ScheduleDttm ) or die "BOOM"; $sth->bind_param( 2, $DepartmentID ) or die "BOOM"; $sth->bind_param( 3, $FileId ) or die "BOOM"; $sth->execute() or die "Can't execute statement: $SelStatement , $DBI:: +errstr"; .... ....
The error is encountered the second time the execute statement is run, first time thru is fine. This leads me to belive its due to some cashed statement not being handled correctly. I don't think it's the code as it runs ok on the SUN box with Perl 5.005_03, but blows up under AIX with Perl v5.8.2. Any and all ideas are welcome, and thanks in advance. Hibbarra

Replies are listed 'Best First'.
Re: DBI problem on AIX/Oracle 10g
by runrig (Abbot) on Jul 24, 2007 at 23:22 UTC
    Are the DBI and DBD versions the same? The Oracle client library versions? It kind of sounds like this problem, but I can't vouch for whether that will help at all. You could try using named parameters instead of '?' placeholders. Just a shot in the dark :-)
Re: DBI problem on AIX/Oracle 10g
by fmerges (Chaplain) on Jul 24, 2007 at 20:14 UTC

    Hi,

    As I don't see the remaining of the code, so I can only guess, but I would try to first check the arguments to the function, and I also would try to prepare the statement after calling $dbh =. BTW you can also say $sth->execute($a, $b, $c).

    Once I used JDBC which uses Inline::Java and it worked fine, but this was because I hadn't the libraries for an old Oracle running on a HP-UX.

    Regards,

    fmerges at irc.freenode.net
Re: DBI problem on AIX/Oracle 10g
by fstat(pipe) (Scribe) on Jul 24, 2007 at 22:52 UTC
    You may want to set the TNS_ADMIN environment variable which is the location of your personal copy of the tnsnames.ora file.
Re: DBI problem on AIX/Oracle 10g
by fmerges (Chaplain) on Jul 24, 2007 at 17:45 UTC

    Hi,

    Why is the scope of the $sth (statement handler) outside of the function, and the $ltqs_dbh (database handler) scope only defined in the function itself? Why are you connecting and disconnecting everytime you call the function?

    Regards,

    fmerges at irc.freenode.net
      $sth is a persistent variable. That way I can use it in the unless statement. The connect to the database uses its own persistent variable if its already defined it just returns it if its not it performs a connect. I am not disconecting and reconnecting, I only connect once. By doing it this way I don't have to pass connection handles all over the place. Hibbarra
Re: DBI problem on AIX/Oracle 10g
by smithers (Friar) on Jul 25, 2007 at 18:30 UTC
    Any chance you are passing in undef for $FileId, $DepartmentId or $ScheduleDttm or the wrong data-types expected by Oracle (e.g. alpha char for an integer file or dept id)? Binding on a null could be an issue with the SQL (need AND DEPT_ID IS NULL for example vs. AND DEPT_ID = NULL). Connectivity (and TNS_ADMIN) does not seem to be the issue here if your first invocation of CheckTransmissionHist is successful. Good luck.
      No they are all defined, checked them in the debugger.
        Do you have Oracle Metalink support access? If so, lookup support Note: 415637.1. If you are running Oracle 10.2.0.3 then this Note identifies an Oracle bug that matches the behavior you reported exactly with the second execute aborting. If you don't have Metalink access, try setting your Oracle db parameter CURSOR_SHARING=EXACT (the workaround mentioned in the support note). The note also implies there is a bugfix for this issue. Get your DBA involved.