FILENAME: run_list_tables.ksh

#!/bin/ksh
d=`date +%Y%m%d`

log_dir=$HOME
output_file=log.list

list_tables -login /@testdb -outputFile $output_file

mailx -s "list report : $d" test@mail < $output_file

##</code><code>##

FILENAME: list_tables

use strict;
use Getopt::Long;

use DBI;
use DBD::Oracle qw(:ora_types);

my $exitStatus = 0;
my %options = ()
my $oracleLogin;
my $outputFile;
my $runDate;
my $logFile;


($oracleLogin, $outputFile) = &validateCommandLine();

my $db = &attemptconnect($oracleLogin);

&reportListTables($outputFile);

$db->($exitStatus);


#---------------------------
sub reportListTables {

	my $outputFile = shift;

	if ( ! open (OUT,">" . $outputfile)) {
	    &logMessage("Error opening $outputFile");
	}

	print OUT &putTitle;

	my $oldDB="DEFAULT";
	my $dbcounter = 0;
	my $i;

	print OUT &putHeader();

	#iterate over results
	for (my $i=0; $i<=$lstSessions; $i++) {
	    # print result row
	    print OUT &putRow($i);
	    $dbCounter++;
	}
	
	print OUT &putFooter($dbCounter);
	print OUT "   *** Report End \n";

	closeOUT;
}


#------------------------------	
sub putTitle {
	my $title = qq{
   List Tables: Yesterday
  --------------
};


#------------------------------
sub putHeader {
	my $header = qq{

   TESTDB
  ==============
	OWNER       Table	Created


};

#------------------------------
sub putRow {

	my $indx = shift;
	my $ln = sprintf "%-19s %-30s %-19s",
	   $lstSessions[$indx]{owner},
	   $lstSessions[$indx]{object_name},
	   $lstSessions[$indx]{created};

return "$ln\n";

}

#------------------------------
sub getListTables {

	my $runDt = shift;
	my $rounter = 0;

	my $SQL = qq{
	selct owner, object_name, to_char(created,'MM-DD-YYYY') from dba_objects
	};

	my $sth = $db->prepare (SQL) or die $db->errstr;

	$sth->execute() or die $db->errstr;;

	while (my @row = $sth->fethcrow_array) {
	$lstSessions[$rcounter] {owner}		=$row[0];
	$lstSessions[$rcounter] {object_name}	=$row[1];
	$lstSessions[$rcounter] {created}	=$row[2];

        &logMessage(" Owner:	$lstSessions[$rcounter]{owner}");
	&logMessage(" Table:	$lstSessions[$rcounter]{object_name}");
	&logMessage(" created:	$lstSessions[$rcounter]{created}");

	$rcoiunter++;
	}

	&logMessage("rcounter records found...");

}