Some people have reported problems with DBI 1.15 + DBD::Oracle 1.06.
Please try using DBI 1.14 and see if that helps.
Please _do_ let me know if that helps.
Tim.
On Thu, Apr 19, 2001 at 02:09:52AM -0500, Hugh Riley wrote:
> 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...
>
> ---------------------------------------------------------
