view release on metacpan or  search on metacpan

PLSQL.pm  view on Meta::CPAN

    # set outdir 
#    if (exists $odr->{$sn}) {
#      $pr->{db_outdir}  = $odr->{$sn}{db}; 
#      $pr->{web_outdir} = $odr->{$sn}{web}; 
#    } else {
#      $pr->{db_outdir}  = $ar->{outdir} if exists $ar->{outdir}; 
#      $pr->{web_outdir} = $ar->{outdir} if exists $ar->{outdir}; 
#    }
    if (! exists $pr->{web_outdir}) {
      $s->echo_msg("ERR: out_dir was not defined.", 0);  return; 
    }
#    $pr->{tgt_dir} = join $ds, $pr->{web_outdir}, $sn, $tsk;
#    $pr->{log_dir} = join $ds, $pr->{tgt_dir}, $pr->{y4}, $pr->{mm};
#    $pr->{sql_fn}  = join $ds, $pr->{log_dir}, "s$pr->{dtm}.sql"; 
    # set db connection
    my $cs = $pr->{cs}; 
    if (!$cs) {
      $s->echo_msg("ERR: ($prg) db connection was not defined.", 0); return; 
    } 
    my $cr = {};					# code array ref
    if ($ar->{preload_code} && !$p->{is_tasksql}) {
      $cr->{plsql_dir} = $ar->{plsql_dir}  if exists $ar->{plsql_dir}; 
      $cr->{plsql_dir} = $ar->{ifn_dir}    if !$cr->{plsql_dir};
      my $pdr = $cr->{plsql_dir}; 
      $s->echo_msg("INFO: ($prg) loading codes from $pdr...", 3);
      if (-d $pdr) {
        opendir DD, "$pdr" or croak "ERR: could not opendir - $pdr: $!\n";
        my @a = sort grep !/\.bak$/, (grep !/^\./, readdir DD);
        # map {  $s->read_plsql("$pdr/$_",$cr); } sort grep !/\.bak$/, (grep !/^\./, readdir DD);
        closedir DD; 
        for my $x (0..$#a) { my $f = $a[$x]; 
        $s->read_plsql("$pdr/$f",$cr); }
        $s->echo_msg($cr,9); 
      } else {
        $s->echo_msg("ERR: ($prg) could not find plsql code dir - $pdr", 0);
      }
    }
    ($pr->{mtr_sch}) = ($cs =~ /^(\w+)\//); 		# db schema 
    if (!$p->{is_tasksql}) {
      #           $txt =~ s/,\s*/,\n/g;        
      $tsk_txt =~ s/;\s*/;\n/g;				# add line break back
      $tsk_txt =~ s/(declare|begin)/$1\n/ig; 		# add line break
      $tsk_txt = $s->expand_vars($tsk_txt, $pr);	# expand param variables
      $tsk_txt = $s->expand_vars($tsk_txt, $ar);	# expand param variables      
      $tsk_txt =~ s/(\'?\$a\d+\'?)/null/g;		# nullify the missing $a1,$a2,
      $tsk_txt =~ s{\/\;\s*}{\/\n}g; 			# remove ; for /;
      $tsk_txt =~ s/(\s*,\s*\w+\s*=>)/\n  $1/ig;	# add line break
      $tsk_txt =~ s/\s+(where|order by|group by)/\n  $1/ig;	# add line break
      $tsk_txt =~ s/(union all|union)/\n$1\n/ig;	# add line break
      $s->echo_msg("TXT: <pre>\n$tsk_txt\n</pre>",3);
    }

    # 4. set output directories
    $s->echo_msg("4. setting output directories...", 2);
    # mkdir $pr->{out_dir}, 0777 if (! -d $pr->{out_dir});
    # mkdir $pr->{tgt_dir}, 0777 if (! -d $pr->{tgt_dir});
    if (! -d $pr->{log_dir}) { 
      eval { mkpath($pr->{log_dir},0,0777) };
      croak "ERR: could not mkdir - $pr->{log_dir}: $!: $@<br>\n" if ($@);
      if ($^O !~ /^MSWin/i) { 				# non window
        system("chmod -R ugo+w $pr->{log_dir}"); 
      }
    } 
    my $radr = $ENV{REMOTE_ADDR}; $radr =~ s/\./_/g;
    my $tmpd = join $ds,$pr->{web_outdir},$sn,$radr,$pr->{y4},$pr->{mm},$pr->{dd};
    if (! -d $tmpd && ! $pr->{is_tasksql}) {
      eval { mkpath($tmpd,0,0777) };
      croak "ERR: could not mkdir - $tmpd: $!: $@<br>\n" if ($@);
      if ($^O !~ /^MSWin/i) { 				# non window
        system("chmod -R ugo+w $tmpd"); 
      }
    } 
    my $tpf1 = join $ds, $tmpd, "${tsk}_$pr->{tm}.sql"; 

    # 5. compose command
    $s->echo_msg("5. composing commands...", 2);
    my $rc  = {'0'=>'OK', '1'=>'Command is not invoked', '2'=>'Failed'};
    my ($svr,$usr,$pwd,$ohm) = ();
    my $vhm = 'ORACLE_HOME'; 
       $ohm = $ENV{$vhm} 	if exists $ENV{$vhm} && $ENV{$vhm}; 
       $ohm = $scn->{orahome}	if !$ohm && exists $scn->{orahome}; 
       $svr = $scn->{svr}	if exists $scn->{svr};
       $usr = $scn->{usr}	if exists $scn->{usr};
       $pwd = $scn->{pwd}	if exists $scn->{pwd};
    # my ($svr,$usr,$pwd,$ohm) = 
    #    ($scn->{svr},$scn->{usr},$scn->{pwd},$scn->{orahome});

    my $cfn  = join $ds, $ohm, 'bin', 'sqlplus';	# command file name
       $cfn .= ".exe" 	if ($^O =~ /^MSWin/i); 
    $s->echo_msg("INFO: ($prg) CFN set to $cfn", 2);        
    # if (! -f $cfn) {
    #   $s->echo_msg("ERR: could not find - $cfn", 0); return; 
    # } 
    $pr->{sql_cfn} = $cfn;				# command file name
    $pr->{sql_cs}  = $cs;				# connection string
    # keep the variables 
    $ar->{sql_cfn} = $cfn; 				# command file name
    $ar->{sql_cs}  = $cs; 				# connection string
    $s->echo_msg($pr, 4);
    my ($r1, @a) = ();
    if ($^O !~ /^MSWin/i) { 				# non window
       ($r1, @a) = rexec($svr, 'ls -l $cfn', $usr, $pwd);
       $s->echo_msg("INFO: ($prg) R1: $r1 - $rc->{$r1}", 3); $s->echo_msg(\@a,3);
      if ($r1 > 0) {
          $s->echo_msg("ERR: ($prg) could not run command - $cfn: $!.", 0);
          return;
      }
    }
    my $cmd;
    if ($^O !~ /^MSWin/i) { 			# non window
       $cmd = "ORACLE_HOME=$ohm;\nexport ORACLE_HOME;\n";
    } else {					# window
       $cmd = '';
    }
    if ($p->{is_tasksql}) {
      $cmd .= "$cfn -S $cs \@$tsk_fn";
    } else {
        $s->echo_msg("TXT: <pre>\n$tsk_txt\n</pre>",3);
        $s->echo_msg("INFO: ($prg) Writing SQL to $tpf1...",2);
        open  TMPSQL,">$tpf1" or carp "ERR: could not write to $tpf1: $!\n";
        print TMPSQL "$tsk_txt\nexit;\n";
        close TMPSQL; 
        if ($cr) { 
          $s->expand_code($tpf1, $cr);		# expand code variables              
          $s->expand_code($tpf1, $cr);		# expand code variables 
          $s->expand_code($tpf1, $cr);		# expand code variables 
        } 
        $cmd .= "$cfn -S $cs \@$tpf1"; 
        croak "ERR: did not find sql file - $tpf1<br>\n" if ! -f $tpf1; 
    }