First of all, I am new to perl, so be gentle with me. Any assistance would
be greatly appreciated
I'm having a problem with a perl script that uses DBI (1.15) with the
Oracle DBD (1.06) that seems to be tied to the number of fields I want
returned and the number of rows that are returned. Essentially, the script
is hanging on the fetch of the data. The database is on another server in
the same subnet.
In the SELECT, there's a ROWNUM limit. If I remove it, same problem. If I
drop it down to 100, then no problems. Before I put the RowCacheSize
parameter in the connect string, the script would hang at the execute
portion (after the "dbd_st_execute SELECT (out0, lob0)..." bit in the
dbitrace.log)
If I decrease the number of fields, I can increase the number of rows I get
back. However, the number of rows I can retrieve with the current number of
fields seems to be slowly decreasing. The first time I actually figured out
how many rows I could get back, it was at 517. Now, it's somewhere around 475.
I've tried modifying the way that I retrieve the data (fetch with
bind_colums vs. fields with fetchrow_array vs. etc.), the RowCacheSize and
others, but nothing seems to do anything.
Again, thanks for any assistance any of you can provide.
Here's a test script I created so I can work on the problem while not doing
everything the main script does:
--------------------------------------------------------------------------------------
#!/usr/bin/perl -w
BEGIN {
$ENV{ORACLE_HOME} = '/usr/local/OraHome';
$ENV{TWO_TASK} = 'SID';
}
use DBI;
use DBD::Oracle;
my $sid="SID";
my $user="user";
my $pass="pass";
DBI->trace( 4, 'dbitrace.log' );
my $dbh = DBI->connect("DBI:Oracle:$sid",$user,$pass, {RaiseError => 1,
RowCacheSize => -1});
my $strSQL = 'SELECT field1, field2, field3, field4, field5, field6,
field7, field8, field9, field10, field11, field12, field13, field14,
field15, field16, field17 FROM table_name WHERE rownum <= 1000 AND
otherfield IS NULL ORDER BY field5';
my $sth = $dbh->prepare ($strSQL);
$sth->execute();
my( $field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8,
$field9, $field10, $field11, $field12, $field13, $field14, $field15,
$field16, $field17 );
$sth->bind_columns( \($field1, $field2, $field3, $field4, $field5, $field6,
$field7, $field8, $field9, $field10, $field11, $field12, $field13,
$field14, $field15, $field16, $field17) );
while ($sth->fetch) {
print "$field1\n";
}
$sth->finish;
$dbh->disconnect();
--------------------------------------------------------------------------------------
and here's the result from the dbitrace.log:
--------------------------------------------------------------------------------------
DBI 1.15-nothread dispatch trace level set to 4
-> DBI->connect(DBI:Oracle:SID, user, ****, HASH(0x80f86e0))
-> DBI->install_driver(Oracle) for perl=5.006 pid=26311 ruid=560 euid=560
install_driver: DBD::Oracle loaded (version 1.06)
New DBI::dr (for DBD::Oracle::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x816e0fc)=>DBI::dr=HASH(0x81bd828),
DBD::Oracle::dr, 0, Null!)
dbih_make_com(Null!, DBD::Oracle::dr, 88)
<- install_driver= DBI::dr=HASH(0x816e0fc)
-> connect for DBD::Oracle::dr (DBI::dr=HASH(0x816e0fc)~0x81bd828
'SID' 'user' **** HASH(0x81bc578))
New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x81bd828), id=)
dbih_setup_handle(DBI::db=HASH(0x81bc818)=>DBI::db=HASH(0x81bd81c),
DBD::Oracle::db, 816de50, Null!)
dbih_make_com(DBI::dr=HASH(0x81bd828), DBD::Oracle::db, 112)
<- connect= DBI::db=HASH(0x81bc818) at DBI.pm line 412.
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
'RaiseError' 1)
STORE DBI::db=HASH(0x81bd81c) 'RaiseError' => 1
<- STORE= 1 at DBI.pm line 437.
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
'PrintError' 1)
STORE DBI::db=HASH(0x81bd81c) 'PrintError' => 1
<- STORE= 1 at DBI.pm line 437.
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
'AutoCommit' 1)
<- STORE= 1 at DBI.pm line 437.
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
'RowCacheSize' -1)
<- STORE= 1 at DBI.pm line 441.
<- connect= DBI::db=HASH(0x81bc818)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x81bc818)~0x81bd81c
'SELECT field1, field2, field3, field4,
field5, field6, field7, field8, field9, field10, field11, field12, field13,
field14, field15, field16, field17
FROM table_name WHERE rownum <= 1000 AND otherfield IS NULL ORDER BY field5')
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x81bd81c), id=)
dbih_setup_handle(DBI::st=HASH(0x81bc83c)=>DBI::st=HASH(0x81bc590),
DBD::Oracle::st, 81bd804, Null!)
dbih_make_com(DBI::db=HASH(0x81bd81c), DBD::Oracle::st, 204)
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'FIELD1' NULLable, otype 1-> 5, dbsize 65/66, p65.s0
fbh 2: 'FIELD2' NULLable, otype 1-> 5, dbsize 7/8, p7.s0
fbh 3: 'FIELD3' NULLable, otype 1-> 5, dbsize 50/51, p50.s0
fbh 4: 'FIELD4' NULLable, otype 1-> 5, dbsize 50/51, p50.s0
fbh 5: 'FIELD5' NULLable, otype 2-> 5, dbsize 22/134, p7.s0
fbh 6: 'FIELD6' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 7: 'FIELD7' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 8: 'FIELD8' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 9: 'FIELD9' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 10: 'FIELD10' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 11: 'FIELD11' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 12: 'FIELD12' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 13: 'FIELD13' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 14: 'FIELD14' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 15: 'FIELD15' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 16: 'FIELD16' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 17: 'FIELD17' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
dbd_describe'd 17 columns (row bytes: 206 max, 188 est avg, cache: -1)
<- prepare= DBI::st=HASH(0x81bc83c) at ortest.pl line 20.
-> execute for DBD::Oracle::st (DBI::st=HASH(0x81bc83c)~0x81bc590)
dbd_st_execute SELECT (out0, lob0)...
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at ortest.pl line 22.
-> bind_columns in DBD::_::st for DBD::Oracle::st
(DBI::st=HASH(0x81bc83c)~0x81bc590 SCALAR(0x81baef0) SC
ALAR(0x81baf08) SCALAR(0x81baf20) SCALAR(0x81baf38) SCALAR(0x81baf50)
SCALAR(0x81baf68) SCALAR(0x81baf80) SCA
LAR(0x81baf98) SCALAR(0x81bafb0) SCALAR(0x81bafc8) SCALAR(0x81bafe0)
SCALAR(0x81bc464) SCALAR(0x81bc47c) SCAL
AR(0x81bc494) SCALAR(0x81bc4ac) SCALAR(0x81bc4c4) SCALAR(0x81bc4dc))
dbih_setup_fbav for 17 fields => 0x81f327c
dbih_sth_bind_col 1 => SCALAR(0x81baef0)
dbih_sth_bind_col 2 => SCALAR(0x81baf08)
dbih_sth_bind_col 3 => SCALAR(0x81baf20)
dbih_sth_bind_col 4 => SCALAR(0x81baf38)
dbih_sth_bind_col 5 => SCALAR(0x81baf50)
dbih_sth_bind_col 6 => SCALAR(0x81baf68)
dbih_sth_bind_col 7 => SCALAR(0x81baf80)
dbih_sth_bind_col 8 => SCALAR(0x81baf98)
dbih_sth_bind_col 9 => SCALAR(0x81bafb0)
dbih_sth_bind_col 10 => SCALAR(0x81bafc8)
dbih_sth_bind_col 11 => SCALAR(0x81bafe0)
dbih_sth_bind_col 12 => SCALAR(0x81bc464)
dbih_sth_bind_col 13 => SCALAR(0x81bc47c)
dbih_sth_bind_col 14 => SCALAR(0x81bc494)
dbih_sth_bind_col 15 => SCALAR(0x81bc4ac)
dbih_sth_bind_col 16 => SCALAR(0x81bc4c4)
dbih_sth_bind_col 17 => SCALAR(0x81bc4dc)
<- bind_columns= 1 at ortest.pl line 26.
-> fetch for DBD::Oracle::st (DBI::st=HASH(0x81bc83c)~0x81bc590)
dbd_st_fetch 17 fields...
---------------------------------------------------------
