Re: SQL*Plus / Report Script for Table Information

Here is a little bit of html I wrote to find all the tables in the database matching userx. All the table names are hypertext links to the table and there fields. This uses DBI and Perl. It does not do everything you want but might be a good starting point. michael

File #1. describe.cgi
!/usr/local/bin/perl -w

use DBI;
use strict;
# set up Oracle ENV variables,
%ENV = (

        ORACLE_HOME => '/home/oracle',
        ORACLE_SID => 'YOUR_SID',
       );

print "Content-type: text/html \n\n";

my $drh = DBI->install_driver( 'Oracle' );

my $dbh = $drh->connect( 'YOUR_SID', 'username','password' );

my $cursor = $dbh->prepare( "select table_name from all_tables where owner = 'SOMEUSER' " );
print "<table border=1 cellpadding=2 cellspacing=2>\n"; print "<TR>\n <Th>table_name\n ";
my ($first);
$cursor->execute;

while ( ($first) = $cursor->fetchrow ) { print "<TR> \n <TD><a href=\"describe_table.cgi?$first\">$first</A> \n ";

}
print " </table> <br> <hr> \n";
$cursor->finish;
$cursor->finish;

$dbh->disconnect;

exit;

File #2 describe_table.cgi
#!/usr/local/bin/perl -w

BEGIN { push _at_INC, "/usr/local/perl/lib", "/www/htdocs/lib"; } use DBI;
use strict;
# set up Oracle ENV variables,
$ENV{ORACLE_HOME} = '/home/oracle/';
$ENV{ORACLE_SID} = 'ORACLE_SID';
print "Content-type: text/html \n\n";

my $drh = DBI->install_driver( 'Oracle' );

my $dbh = $drh->connect( 'SID', 'USER', 'Password' );

my $myquery;

$myquery = $ENV{'QUERY_STRING'};

my $cursor = $dbh->prepare( "select column_name,data_type,data_length from cols
where table_name = '$myquery'" );

print "<center> <h2> $myquery </H2> \n";
print "<table border=1 cellpadding=5 cellspacing=5>\n";
print "<TR>\n <Th>column name \n <TH>Type \n <TH> size \n";

my ($first, $middle, $last);
$cursor->execute;

while ( ($first, $middle, $last) = $cursor->fetchrow ) { print "<TR> \n <TD>$first \n <TD>$middle \n <TD>$last\n ";

}
print " </table> <br> <hr> \n";
print " </center> </body> </html> \n";
$cursor->finish;

$dbh->disconnect;

exit; Received on Wed Jul 17 1996 - 00:00:00 CEST