Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Perl Script stalling.............

    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?

  2. #2
    Join Date
    May 2004
    Posts
    3
    Rather than using the backticks fork mechanism, direct the output from your sub-process through a file descriptor (OPEN...). Not guaranteeing it will work, but much more controlled mechanism for testing.

  3. #3
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    Do your $ENV-settings within a BEGIN{}-block. So you can ensure your environment is set before at the beginning of the script. Without this BEGIN{}-block you can't be sure...
    Code:
    BEGIN{
    $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";
    }
    I would recommend to you to use
    Code:
    use strict;
    in your script. But then you have to declare all variables with my

    Code:
    #!/bin/perl
    use Getopt::Long;
    use strict;
    use warnings;
    
    BEGIN{
      $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:.";
    
    
      my $TRYPO_HOME="/opt/local/sysops/reports/trypo/weekly";
      my $output_dir="/scratch";
    }
    
    my $START_DATE;
    my $END_DATE;
    
    GetOptions( "s:s" => \$START_DATE,
    "e:s" => \$END_DATE);
    
    dates();
    proc_searches();
    [...]
    board.perl-community.de - The German Perl-Community

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •