Hi all

Here looking for a little help with a script that has got me confused

The purpose of the script is to basically log on to a number of databases with a number of different schema owners and create a stats table containing the row count of all tables under that schema owner and the print the records of that stats table to a file

The script is to then go through each table and print either all records if the table has 500 or less rows, or it should work out the middle 500 rows and print them, to an extract file

This seems to be what is happening, except there are number of errors written to the screen when the script is run

I will first post the code of the script

#!/usr/local/bin/perl -w
use strict;
use DBI;                # for database connectivity

my $rv;

# Hash for DB connectivity
my %db_conn = (
        A_MANAGER =>['DBI:Oracle:host=ahostname;sid=ADB01;port=5555','
+a_manager','a_manager',],
        B_MANAGER =>['DBI:Oracle:host=ahostname;sid=BDB01;port=5555','
+b_manager','b_manager',],
           );

# Loop for each db schema owner in the db conn hash to run each query 
+against
for my $owner (keys %db_conn) {

    # File Handle for STATS_TABLE output for each owner
    open(FH, ">STATS_TABLE_${owner}_output") or
    die "\n cannot write to the file output file. Check the file permi
+ssions";

    # Array of query names to order query hash
    my @querynum = ("query1","query2","query3","query4");

        # Loop for each query
        foreach my $query (@querynum) {

        # Interactive print to inform user which query is being perfor
+med
        print "Performing $query \n";

        # Hash of all queries
        my %querylist = (
               query1 => "BEGIN
               EXECUTE IMMEDIATE 'DROP TABLE STATS_TABLE_$owner';
            EXCEPTION
               WHEN OTHERS THEN
                  IF SQLCODE != -942 THEN
                 RAISE;
                  END IF;
            END;",

               query2 => "CREATE TABLE STATS_TABLE_$owner
            (TABLE_NAME VARCHAR2(50),
            OWNER VARCHAR2(20),
            RECORD_COUNT NUMBER)
            TABLESPACE STS_DAT",

               query3 =>"declare
                v_count integer;
            begin

                for r in (select table_name, owner from all_tables
                          where owner ='$owner' and table_name <> 'STA
+TS_TABLE')
                loop
                execute immediate 'select count(*) from ' || r.table_n
+ame
                    into v_count;
                INSERT INTO STATS_TABLE_$owner(TABLE_NAME,OWNER,RECORD
+_COUNT)
                VALUES (r.table_name, r.owner, v_count);
                end loop;

            end;",

               query4 => "select table_name, record_count from STATS_T
+ABLE_$owner"
            );

        # Assigning the sql query referenced in the query hash
        my $sql = $querylist{$query};

        # Assigning the db conn details from the db conn hash relevant
+ to the schema owner currently being exercised
        my @data_source = @{ $db_conn{$owner} };
    
        ##print "@data_source\n";

        # Performing database connection
        my $dbh = DBI->connect(@data_source) or die "Couldn't connect 
+to database: " . DBI->errstr;

        # Perfroming the prepare of the current query
        my $sth = $dbh->prepare($sql)
                    or die "Couldn't prepare statement: " . $dbh->errs
+tr;

        # Executing the current query
        $sth->execute() or die "Couldn't execute statement: " . $sth->
+errstr;

        # If the current query is query 4 then do this additional proc
+essing
        if ($query eq "query4") {

            # Declaring variables for columns
            my ($tb_name, $rec_count);

            # Bind Perl variables to columns:
              $rv = $sth->bind_columns(\$tb_name, \$rec_count);

             # Column binding to fetch data
            while ($sth->fetch) {
                
                # File handle for data extract
                open(FHExtract, ">${tb_name}_extract") or
                die "\n cannot write to the file output file. Check th
+e file permissions";

                # If the table being queried has a row count greater t
+han 500 rows then we want to grab the middle 500 rows
                if ($rec_count > 500) {

                    # We use int as our rounding method. This is not t
+rue rounding though as it only drops the decimal
                    my $min_rec_count = int($rec_count/2-250);
                    my $max_rec_count = int($rec_count/2+250);

                    # Defining a variable with the required select to 
+guarantee the order based on the index of the table
                    my $extractsql = "select * from
                    ( select a.*, ROWNUM rnum from
                      ( select * from $tb_name
                    order by (select column_name from user_ind_columns
+ where TABLE_NAME='$tb_name' and COLUMN_POSITION='1') ) a
                      where ROWNUM <= $max_rec_count)
                    where rnum  >= $min_rec_count";

                    # Performing the prepare of the query
                    my $sth1 = $dbh->prepare($extractsql)
                    or die "Couldn't prepare statement: " . $dbh->errs
+tr;

                    # Executing the query
                    $sth1->execute() or die "Couldn't execute statemen
+t: " . $sth->errstr;

                    # Declaring the ref variable to process the output
                    my $ref;

                    # Looping through each row of the return and print
+ing it to earlier defined file handle for data extract
                    while($ref = $sth1->fetchrow_hashref) {
                        print FHExtract join (", ", values %$ref), "\n
+";

                        # Closing the statement handler
                        $sth1->finish;
                        }
                    }

                # If there are not over 500 rows then do this processi
+ng, i.e. select all rows
                else {
                    # Defining a variable with the required select
                    my $extractsql1 = "select * from $tb_name";

                    # Performing the prepare of the query
                    my $sth2 = $dbh->prepare($extractsql1)
                    or die "Couldn't prepare statement: " . $dbh->errs
+tr;

                    # Executing the query
                    $sth2->execute() or die "Couldn't execute statemen
+t: " . $sth->errstr;

                    # Declaring the ref1 variable to process the outpu
+t
                    my $ref1;

                    # Looping through each row of the return and print
+ing it to the earlier defined file handle for data extract
                    while($ref1 = $sth2->fetchrow_hashref) {
                        print FHExtract join (", ", values %$ref1), "\
+n";

                        # Closing the statement handler
                        $sth2->finish;
                        }
                
                # Closing the file handler for data extract
                close FHExtract;
                    }
            
            # Printing the contents of the stats table to the earlier 
+defined file handle for the stats table contents
            print FH "$tb_name: $rec_count\n";

            }

            # Closing the statement handler
            $sth->finish;
        }

        # Disconnecting from the database
        $dbh->disconnect if defined($dbh);
    }

    # Closing the file handler for stats table contents
    close FH;

}
[download]

These are the errors (an example of)

DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m STS_VCODE"] at dbmigration.pl line 151.
Use of uninitialized value $ref in join or string at dbmigration.pl li
+ne 124.
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m
                                        ( select a.*, ROWNUM rnum from
                                          ( select * from M_AUDIT_ACK
                                        order by (select column_name f
+rom user_ind_columns where TABLE_NAME='M_AUDIT_ACK' and COLUMN_POSITI
+ON='1') ) a
                                          where ROWNUM <= 1203)
                                        where rnum  >= 703"] at dbmigr
+ation.pl line 127.
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m FI_S_D_DATA"] at dbmigration.pl line 151.
Use of uninitialized value $ref in join or string at dbmigration.pl li
+ne 124.
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m
                                        ( select a.*, ROWNUM rnum from
                                          ( select * from PENDING
                                        order by (select column_name f
+rom user_ind_columns where TABLE_NAME='PENDING' and COLUMN_POSITION='
+1') ) a
                                          where ROWNUM <= 5723)
                                        where rnum  >= 5223"] at dbmig
+ration.pl line 127.
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m ST_SEC_CODE"] at dbmigration.pl line 151.
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing 
+(perhaps you need to call execute first) [for Statement "select * fro
+m PARTITIONLOG"] at dbmigration.pl line 151.
[download]

Any help greatly appreciated. Thanks Chris