Would be helpful it you told us your DBD::Oracle and Oracle Client
version as well
There are limits on what you can do with a remote view id the view has a
LOB.
Does your view have a CLOB or BLOB?
Deepening on your DBD::Oracle version we might be able to a very
detailed OCI trace on what is going on.
Cheers
John Scoles
Croteau, Sylvain wrote:
Hello,
I got a strange problem, and I need help to solve this.
I need to run an Oracle stored proc from perl. So far, nothing exceptionnal.
The problem is that I'm using a db link in the Oracle stored proc. I can run
it successfully from Toad and sqlplus, but I'm having issue when run from perl.
When run from sqlplus, I got around 63215 rows (more or less, depending of the
day) coming from the db link and inserted in an empty table. When run from
perl, I got between 53 and 150 rows, depending on the day, but the value is the
same all day long. I'm using a slightly modify version of DBI, to enhance the
DBI::CSV, but I'm not using this modification in the current problem.
The only thing I can think of is timeout: the perl run for about 10-11 seconds
(and yield partial result), and the command in sqlplus run in 14 seconds. I've
digged out google, perl monk and cpan sites to find if there was something like
a timeout parameter in the DBI or in the Oracle library, but so far, nothing.
I've also did a test where I did a count() on a same schema table instead of a
remoted view and I got no problem, even with tables much larger than 63k rows,
but that run well under 10 seconds (this is why I'm thinking of a timeout issue)
I've monitored the Oracle connection and I can see that the procedure is
called, and connected with the same user I'm using under sqlplus, and no error
is return (either in the stored proc, or in perl)
Do anyone know if there is such thing as timeout or have suggestion on what I
could check ? So far, the problem is only when I'm using the db link, with a
view that take some time (at least 7 seconds on local Oracle server) before
starting to return data.
Thanks in advance,
Sylvain Croteau
stored proc:
CREATE OR REPLACE PROCEDURE some_procedure
IS
toto number;
BEGIN
select count(*) into toto FROM
a_remote_v...@some_db_link.regress.rdbms.dev.us.oracle.com;
insert into debug_table values ('nb rows view = ' || to_char(toto));
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise;
WHEN OTHERS THEN
insert into debug_table values ('Raise !!!');
RAISE;
END;
perl code:
#!/apps/something/shared/perl/bin/perl
require 5.6.0;
use strict;
use integer;
use Getopt::Std;
use ImportLib::ImportLib;
use ImportLib::DBWrapper;
use ImportLib::Importer;
use FindBin qw( $Bin );
our( $VERSION, $opt_i, $opt_d, $opt_h, $opt_f );
$VERSION = '#1';
my ($dbWrapper, $dbh, @sql, $statement );
my $feedCode;
# ----- Setup initialization file, database connections, and logging
initConfig( $opt_i, '', $opt_d, @ARGV );
$dbWrapper = ImportLib::DBWrapper -> new();
$dbh = $dbWrapper -> getHandle();
print "Connection Completed\n";
$dbh->{AutoCommit}=1;
$dbh->{RaiseError=>1};
print "Setup Completed\n";
my $reccount = 0;
my ($dbPrefix , $psql, $type, $entry );
print " Begin - Preparing data for recon reports \n";
eval { $dbh->do("call some_procedure()"); };
if ($@) {
warn "some_procedure - $dbh->errstr", "\n $...@\n" ;
}
else
{ $dbh->commit; }
print " End - Preparation data for recon \n";
$dbh->disconnect();
$dbWrapper -> disconnect();
