#! /usr/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('DBI:Oracle:R12COE', 'apps', 'app5vis')
    or die "couldn't connect to database: " . DBI->errstr;

my $sth = $dbh->prepare("SELECT DISTINCT UPPER(OBJECT_NAME) FROM
                         CG_COMPARATIVE_MATRIX_TAB
                         WHERE OBJECT_NAME IS NOT NULL
                         ORDER BY 1 ASC")
    or die "couldn't prepare statement: " . $dbh->errstr;

$dbh->{AutoCommit}    =  0;
$dbh->{RaiseError}    =  1;
$dbh->{ora_check_sql} =  0;
$dbh->{RowCacheSize}  = 16;
$sth->execute;
my @obj_name;

for (my $j = 0; my @data = $sth->fetchrow_array(); ++$j)
{
    $obj_name[$j] = $data[0];
}

my $dir = '/u05/oracle/R12COE/spotlighter/Search_Files/Forms';
opendir(my $dh, $dir) or die $!;
my @files = grep { -f "$dir/$_" } readdir $dh;
closedir($dh)         or die $!;

foreach my $file (@files)
{
    my ($ext) = $file =~ /(\.[^.]+)$/;
    open(my $fh, '<', $file) or die "couldn't open $file: $!";
    my @lines = <$fh>;
    close($fh)               or die "couldn't close $file: $!";

    for my $obj_name (@obj_name)
    {
        my $count = grep /$obj_name/, @lines;
        my $sth1  = $dbh->prepare("INSERT INTO
                                   CUSTOM_FILES_SUMMARY(FILE_NAME,
                                   FILE_TYPE,DEP_OBJECT_NAME,OCCURANCE)
                                   VALUES('$file','$ext','$obj_name',$count)")
            or die "couldn't insert statement: " . $dbh->errstr;
        $sth1->execute;
    }
}

$dbh->disconnect;