CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Problem to convert Shell oracle script to Perl ORACLE dynamic

 



digioleg54
User

May 9, 2017, 12:39 PM

Post #1 of 2 (735 views)
Problem to convert Shell oracle script to Perl ORACLE dynamic Can't Post

I have simple shell script with Oracle script

Code
process_info () { 
MONITOR_KEY=$1
OUT_DIR=${2-$OUTPUT_DIR}

if [ ! -z "$MONITOR_USER" ]
then
SQL_SCRIPT=$OUT_DIR/$MONITOR_KEY.process
SQL_LOG=$OUT_DIR/$MONITOR_KEY.process.log
SQL_ERR=$OUT_DIR/$MONITOR_KEY.process.err
cat <<EOD >$SQL_SCRIPT
WHENEVER OSERROR EXIT SQL.OSCODE
WHENEVER SQLERROR EXIT SQL.SQLCODE
set HEADING OFF
set FEEDBACK OFF
set PAGESIZE 0
set TERMOUT ON
set VERIFY OFF
set SERVEROUTPUT ON
select NVL(A.status, 'N/A')
from dte_monitor A, dte_parameter C
where C.parameter_name = 'MONITOR_DATE'
and TRUNC(C.parameter_date) = TRUNC(A.proc_date)
and A.KEY = upper('$MONITOR_KEY');
quit;
EOD

echo "TEST1 $SQLPLUS $MONITOR_USER $SQL_SCRIPT"
RESULT=`$SQLPLUS $MONITOR_USER @"$SQL_SCRIPT"`
if [ $? -ne 0 ]
then
print "ERROR"
return -1
else
if [ "$RESULT" = "COMPLETED" -o "$RESULT" = "HOLIDAY" ]
then
print "SKIP"
else
print "$RESULT"
fi
return 0
fi
fi

}

But when I convert it to Perl I have errors.
Could you please help me to convert it correctly

I tried this, but it is not working

Code
my $this_dbh = &db_connect({'DSN'        => "NYFISWCD"}); 
my $ORACLEHOME = $ENV{'ORACLE_HOME'};
my $monitor = "OLEG";
my $output= $lib_path."/cfg-bin";
my $sql_script = $output."/".$monitor.".process";
ExtUtils::Command::cat <<EOD> $sql_script;
set HEADING OFF
set FEEDBACK OFF
set PAGESIZE 0
set TERMOUT ON
set VERIFY OFF
select NVL(A.status, 'N/A') from dte_monitor A, dte_parameter C
and TRUNC(C.parameter_date) = TRUNC(A.proc_date)
and A.KEY = upper('$MONITOR_KEY');
quit;
EOD

Here are errors

Code
syntax error at tests.pl line 73, near "set TERMOUT " 
Bareword "ExtUtils::Command::cat" not allowed while "strict subs" in use at tests.pl line 69.
Bareword "EOD" not allowed while "strict subs" in use at tests.pl line 69.
Bareword "quit" not allowed while "strict subs" in use at tests.pl line 70.
Bareword "EOD" not allowed while "strict subs" in use at tests.pl line 79.
Execution of tests.pl aborted due to compilation errors.


Now I made changes below

Code
#!/usr/bin/dwebenv perl 

#use lib "/vhosts/dw18637/lib/web/";

use DBI;
use DBD::Oracle;
use Spreadsheet::ParseExcel;
use File::Basename;
use CGI::Carp qw(fatalsToBrowser);
package ExtUtils::Command;
use strict;
use warnings;

my($lib_path,$data_path);
BEGIN{
$lib_path=$ENV{'DWEB_SITE_PATH'};
$data_path=$ENV{'DWEB_SITE_CGI_DATADIR'};
}
use lib $lib_path.'/lib/web';

print "Content-type: text/html\n\n";
require "dteacl_b.pl";
my $this_dbh = &db_connect({'DSN' => "NYFISWCD"});
my $ORACLEHOME = $ENV{'ORACLE_HOME'};
my $monitor = "OLEG";
my $output= $lib_path."/cfg-bin";
my $sql_script = $output."/".$monitor.".process";
open (my $fh, '>>', $sql_script) or die "Cannot open $sql_script\n";

print $fh "set HEADING OFF\n";
print $fh "set FEEDBACK OFF\n";
print $fh "set PAGESIZE 0\n";
print $fh "set TERMOUT ON\n";
print $fh "set VERIFY OFF\n";
print $fh " select NVL(A.status, 'N/A')
from dte_monitor A, dte_parameter C
where C.parameter_name = 'MONITOR_DATE'
and TRUNC(C.parameter_date) = TRUNC(A.proc_date)
and A.KEY = upper('FINTPREPL')\n";
print $fh "quit\n";

close $fh;

Now the error

Code
Cannot open /vhosts/dw18637/cfg-bin/OLEG.process


Please somebody look at my code and let me know an error, or what can I read how to make this dynamic script.
I cannot find any example or tutorial

thanks,


(This post was edited by digioleg54 on May 10, 2017, 7:04 AM)


digioleg54
User

May 11, 2017, 4:18 AM

Post #2 of 2 (686 views)
Re: [digioleg54] Problem to convert Shell oracle script to Perl ORACLE dynamic [In reply to] Can't Post

When you give me advise? Is it so complicated?

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives