SELECT table_name FROM user_tables

##</code><code>##

SELECT column_name from ALL_CONS_COLUMNS WHERE owner= ? and table_name = ?

##</code><code>##

#!/usr/bin/perl -w

# utility to generate Docbook tables from
# Oracle database

# first rev, Oct. 2001

use strict;
use DBI;

# set up translation table to go from oracle numeric codes
# to nice user-readable strings.  

# yes, DBD::Oracle qw(:ora_types); does the reverse, so I
# should probably have used that.  Next rev., 'k?

my %trans;
my @keys = (1..12);
my @values = qw(SQL_CHAR SQL_NUMERIC NUMBER SQL_INTEGER SQL_SMALLINT SQL_FLOAT
SQL_REAL SQL_DOUBLE DATE SQL_TIME SQL_TIMESTAMP VARCHAR2);
@trans{@keys} = @values;
$trans{-9112} = "CLOB";

# fields for which to ignore the precision

my %no_pres = ( DATE=>1, CLOB=>1 );

#foreach ( sort keys %trans ) {
#   print "$_ => $trans{$_}\n";
#}

# obviously, the next two lines need a-hackin'
$ENV{'ORACLE_HOME'} = "/usr/oracle";

my $db = DBI->connect('dbi:Oracle:host="oracle.host.com";sid="SID"',
                    'username',
                    'password',
                    {RaiseError=>1,
                    AutoCommit=>0});

my $sth = $db->prepare("SELECT * FROM $table_name")
    or die "Can't prepare statement: $db->errstr\n";
$sth->execute();

open OUTPUT, ">output.xml" or die "Can't open output.xml for write: $!\n";
select OUTPUT;

print <<END_OF_HEADER;
<table>
 <title>Table Description for $table_name</title>
 <tgroup cols="2">
  <thead>
   <row>
    <entry align="center"><emphasis role="bold">Column Name</emphasis></entry>
 <entry align="center"><emphasis role="bold">Column Type</emphasis></entry>
   </row>
  </thead>
  <tbody>
END_OF_HEADER

my $i = $sth->{NUM_OF_FIELDS};
for my $field ( 0 .. $i-1 ) {
    print "  <row>\n";
    print "   <entry align=\"center\">$sth->{NAME_lc}->[$field]</entry>\n";
    my $ft = $sth->{TYPE}->[$field];
    if ( exists($trans{$ft}) ) {
         print "   <entry align=\"center\">$trans{$ft}";
         print "($sth->{PRECISION}->[$field])" unless $no_pres{$trans{$ft}};
         print "</entry>\n";
    } else {
        print "Unknown type $ft</entry>\n";
    }
    print "  </row>\n";
}

print " </tgroup>\n </tbody>\n</table>\n";
$sth->finish();
close OUTPUT;
select STDIN;
$db->disconnect;

##</code><code>##

perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); $rose = "smells sweet to degree $n"; *other_name = *rose; print "$other_name\n"'