#!/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 permissions";

	# 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 performed
		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 <> 'STATS_TABLE')
    			loop
	        	execute immediate 'select count(*) from ' || r.table_name
        	    	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_TABLE_$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->errstr;

		# 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 processing
		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 the file permissions";

				# If the table being queried has a row count greater than 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 true 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->errstr;

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

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

					# Looping through each row of the return and printing 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 processing, 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->errstr;

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

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

					# Looping through each row of the return and printing 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;

}