Hello,

I have created this perl script to call my sql scripts in Oracle 9.2.

Perl script:
#!/bin/perl
use Getopt::Long;

GetOptions( "s:s" => \$START_DATE,
"e:s" => \$END_DATE);


$ENV{"ORACLE_HOME"}="/orahome";
$ENV{'ORACLE_SID'}="buzz";
$ENV{'PERL5LIB'}="/usr/local/lib/perl5/5.8.1/sun4-solaris:/usr/local/lib/perl5/5
.8.1:/usr/local/lib/perl5/site_perl/5.8.1/sun4-solaris:/usr/local/lib/perl5/site
_perl/5.8.1:/usr/local/lib/perl5/site_perl:/opt/webserver/modules:.";


$TRYPO_HOME="/opt/local/sysops/reports/trypo/weekly";
$output_dir="/scratch";

dates();
proc_searches();
#proc_qualified_searches();
#proc_other_events();
#proc_operations_report();
#proc_product();
#proc_from_tag();
"weekly.pl" 310 lines, 12459 characters

sub get_date {
$START_DATE=`dosql.pl rp/rp\@buzz "select to_char((sysdate-to_char((sysdate-1),
'D')-7), 'MM/DD/YYYY') from dual"`;
$END_DATE=`dosql.pl rp/rp\@buzz "select to_char((sysdate+(6-to_char((sysdate-1),
'D'))-7), 'MM/DD/YYYY') from dual"`;
chomp($START_DATE);
chomp($END_DATE);
}

sub proc_searches
{
@searches =`/orahome/bin/sqlplus 'rp/rp\@buzz' \@/opt/local/sysops/shells/pro
c/proc_searches.sql '$START_DATE' '$END_DATE'`;
$RESULT=`/opt/local/sysops/shells/dosql.pl rp/rp\@buzz "select to_char(event_
date, 'MM/DD/YYYY'), trypo, per_trypo, yahoo, per_yahoo, swbc, per_swbc, total f
rom rsearches"`;

$RCR_ACTIVITY="Date Trypo % Trypo Yahoo % Yahoo Switchboard % Switchboard
Total\n";
$RCR_ACTIVITY.=$RESULT;

open(RCR_TEMP, "> $output_dir/searches")


The sql file is this:

SET SERVEROUTPUT ON
DECLARE
v_start_date date;
v_end_date date;
v_yend_date date;
v_market_name varchar2(50);
v_state varchar2(2);
v_total number;
v_old_cnt number;

cursor ymarket_cursor is
select market_name, sum(total) total
from tmarket m, psy_searches s
where m.market_code = s.market_code
group by market_name;

cursor smarket_cursor is
select market_name, sum(total) total
from tmarket m, pss_searches s
where m.market_code = s.market_code
group by market_name;

cursor ystate_cursor is
"proc_searches.sql" 214 lines, 6212 characters


Now in the perl script it hangs here:


sub proc_searches
{
@searches =`/orahome/bin/sqlplus 'rp/rp\@buzz' \@/opt/local/sysops/shells/pro
c/proc_searches.sql '$START_DATE' '$END_DATE'`;
$RESULT=`/opt/local/sysops/shells/dosql.pl rp/rp\@buzz "select to_char(event_
date, 'MM/DD/YYYY'), trypo, per_trypo, yahoo, per_yahoo, swbc, per_swbc, total f
rom rsearches"`;

BUT

if I run it straight from Solaris box, it runs in a few seconds.

What am I doing wrong?