Execute oracle sql query in PERL script while loop

Question

I am trying to execute an oracle sql query in while loop of a PERL script as follows --

    my $dbh = DBI->connect( 'dbi:Oracle:'.$dbname,
        $dbusername,
        $pass,
        {   PrintError => 0,
            RaiseError => 1
        }   
    ) || die "Erreur lors de la connexion: $DBI::errstr";

    print ("Connexion à la base de données $dbname avec $dbusername OK \n");

    $requete = "select distinct to_char(date_appli,'yyyymmdd') from DATE_APPLI ".
           "where frequence = 'Q' and actif = 1";

    $sth_sql = $dbh->prepare($requete);
    $sth_sql->execute();
    @row=$sth_sql->fetchrow_array;
   $datesitu=@row[0];

$sth_sql->finish;
print "La date de situation est $datesitu \n";

# SQL to get validation script and table names 
$requete = "select SCRIPT_NAME, table_name from fdf_scripts";

$sth_sql = $dbh->prepare($requete);
$sth_sql->execute();
$sth_sql->bind_columns(undef, \$script_name, \$table_name);

while ($sth_sql->fetch()) {

  $script_sql="$sql_path\\"."FDF_Test_scripts\\".$script_name.".sql";   #validation script path
  $script_log="$log_path\\".$script_name.".log";                        #log files path 
  $rep_file_name="$sql_path\\"."FDF_Test_scripts\\".$table_name.".sql"; #reports SQL path
  $csv_file="$sql_path\\"."FDF_Test_scripts\\".$table_name.".csv";      #report CSV path 

#Load data into validation tables using validation scripts  

  $CmdText="sqlplus -s $connect \@$script_sql $script_log";

  print "Inserting data into table : $table_name \n";

  #system ("$CmdText");

  $col_sql = "select COLUMN_NAME from all_tab_cols where TABLE_NAME = upper(\'$table_name\')\n";

  print "$col_sql\n";

  $sth_sql = $dbh->prepare($col_sql);
  $sth_sql->execute();
  $sth_sql->bind_columns(undef, \$COLUMN_NAME);

  while ($sth_sql->fetch()) {

    print "$COLUMN_NAME\n";

  }



  if (open (my $fh, '<:encoding(UTF-8)', $script_log)){
    while (my $line = <$fh>){
      if ($line=~m/\bERROR\b/){ 
        print "Error While Loading $table_name table Please Check log file for errors at: $script_log \n";
      }       
    }
  }
  else {
        warn "Could open file: $script_log \n"
  }

}

in above code i am fetching table names from a table and then looping them to get the column names of each table which is present in that table.

The code got executed for one iteration of inner while loop but throw error for next iteration as below --

DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select COLUMN_N\
AME from all_tab_cols where TABLE_NAME = upper('FDF_Bond_validation_results')

I am not proficient in PERL and just started to using this and not sure how can i overcome this problem.

Please let me know if more information is required.

What happens if you use a different $db object for the inner SQL query? — choroba
– choroba, Commented Feb 26, 2019 at 9:24
I did not exactly got while you are referring to as different $DB object. you mean to say oracle object or PERL object. I ma new to PERL so may not understand the terminologies used here. I will try to open another DB connection for inner loop as $DBH1 then will see what happens. — mradul
– mradul, Commented Feb 26, 2019 at 9:30
Your problem is that you are assigning to $sth_sql in your loop which is overwriting your loop variable. Either rename one of the variables or make the inner one a my variable. See perldoc -f my. PS the language is Perl calling it PERL will annoy people. — JGNI
– JGNI, Commented Feb 26, 2019 at 9:31
thanks for your comment i will try to use a different variable here. and as suggested will Perl from now on :) — mradul
– mradul, Commented Feb 26, 2019 at 9:35
@JGNI - I tried with new varibale as $sth_sql1 for inner loop and it working fine now however did not use "my" keyword for this. thanks for the help. — mradul
– mradul, Commented Feb 26, 2019 at 9:49

Sylvain · Accepted Answer · 2019-02-26 14:14:11Z

1

In the loop, the line $sth_sql = $dbh->prepare($col_sql); overwrite the content of the $sth_sql variable. The second time the loop run, the content of $sth_sql isn't the same and the statement that overwrote it has already been exhausted (trying to fetch from it again is what causes the error).

To correct this issue you should either use a different name for the second $sth_sql variable or simply declare the second $sth_sql variable with my to keep it inside the loop (see perldoc -f my).

Note: This solution has been identified by JGNI in the comment of the question. My role has only be to summarize the issue to provide a proper answer to this question.

answered Feb 26, 2019 at 14:14

Sylvain

4177 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mradul Over a year ago

That's correct i am now using a different variable name inside the loop which has already been pointed out by JGNI and i thank him and you for making understand the bits of Perl scripting like the use/scope of "my" keyword.

Collectives™ on Stack Overflow

Execute oracle sql query in PERL script while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related