Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

List DB tables

by homer4all (Acolyte)
on Mar 07, 2013 at 18:38 UTC ( #1022288=perlquestion: print w/replies, xml ) Need Help??
homer4all has asked for the wisdom of the Perl Monks concerning the following question:

Frinds.. this is my first post, will try to provide all details accurately and in the best format...

Final Output: Trying to query oracle db and list all tables created yesterday for particular db and email. If not tables created then email shouldn't send Currently using shell script to connect db, get outputfile from perl and email to user. It runs perfectly fine but sends email even there are no tables created.

Questions: 1. Want to only email user if any tables were created, if no table created then shell script shouldn't send any email?

2. In subroutine "putHeader" how to get db name dynamic as per login db, TEST db under subroutine is hardcoded which is not right?

3. any easier way to include send email part within perl only so as to have only 1 file?

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

PERL Script below

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_o +bjects }; 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..."); }

Replies are listed 'Best First'.
Re: List DB tables
by nikosv (Chaplain) on Mar 07, 2013 at 19:07 UTC
    I am not familiar with Oracle,but is it possible to add a trigger on the system/catalog table that gets fired when an INSERT happens?

    Upon the table's creation, an entry of that table will be inserted into the catalog and subsequently the trigger will fire and raise a database event which would notify any registered event listeners to perform an action. In this case the listener would be your script,and the action would be to send an email

    But I don't know if Oracle supports external database events like Ingres does for example Raise a Database Event

      thanks niko...

      I am really new to perl and currently just trying to run simple sql query on oracle db to identify what all tables created 'sysdate-1' and email only if logfile has any output from query.

Re: List DB tables
by McA (Priest) on Mar 08, 2013 at 09:45 UTC


    your script is not complete as some functions are missing and there are some typos which will cause a failure.

    Anyway some hints:

    1. Put a use warnings; below use strict;.

    2. Call your functions simply with func(bla);, forget the ampersand in front of the function name.

    3. Please format your code consequently. (indent, spaces)

    4. The natural flow of your script could be: connect to db, call function which returns list of object (you can print complex data structures with Data::Dumper), if the list has at least one entry call a function which produces text from the list ( a kind of formatter function), if list is not empty take the generated text and put it to an emailing function.

    Answer first question: Mail only when returned list of objects is not empty.

    Second question:

    sub putHeader { my $db_name = shift; my $header = qq{ $db_name ============== OWNER Table Created }; return $header }

    Third question:

    open my $fh, '|-', 'mailx -s "List report" test@mail' or die "ERROR: C +ouldn't open pipe: $!"; print $fh $generatedMailText or die "ERROR: Couldn't write to pipe: $! +"; close $fh or die "ERROR: Closing pipe failed: $!";

    Best regards

      thanks McA...

      your comments really helped and I was able to get rid of .ksh file and get sendmail part into perl..

      but my current problem is log file contents doesn't gets email only name of file appear in email body part... so what exactly I shoul write for $generatedMailText is it

      $outputFile or log.list_tables

      $outputFile = log.list_tables
      open my $fh, '|-', 'mailx -s "List report" test@mail' or die "ERROR: C +ouldn't open pipe: $!";
      print $fh $generatedMailText or die "ERROR: Couldn't write to pipe: $! +";
      close $fh or die "ERROR: Closing pipe failed: $!";
      Thanks, homer

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1022288]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2017-07-25 11:27 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (370 votes). Check out past polls.