Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Looking for a tool to print ORACLE schema as set of text or HTML pages.

by pmas (Hermit)
on Nov 09, 2001 at 20:31 UTC ( #124397=perlquestion: print w/replies, xml ) Need Help??

pmas has asked for the wisdom of the Perl Monks concerning the following question:

I am looking for a tool/script which can print ORACLE schema as set of text or HTML pages. I looked at CPAN, Google, Perlmonks SuperSerch, SourceForge, to no avail.
I know everything can be done via proper SQL, can be organized and formatted properly, but I just cannot believe that it was not done before.

So if you have some script you can share, please let me know. I can possibly start with them, and add some specifics as we need, and let the next guy have better life.

I was thinking about script which can connect to a database, find out tablespaces, tables, and generate set of HTML pages with tables, fieldnames (with datatypes, formats), indices, constraints, sequences and what not. At least more commonly used combinations...

Any suggestions? Links? Thank you!

pmas
To make errors is human. But to make million errors per second, you need a computer.

  • Comment on Looking for a tool to print ORACLE schema as set of text or HTML pages.

Replies are listed 'Best First'.
Re: Looking for a tool to print ORACLE schema as set of text or HTML pages.
by arturo (Vicar) on Nov 09, 2001 at 22:22 UTC

    There is no such (non-commercial) tool to my knowledge. One can create fancy ERDs with MS-Access or Silverrun (don't ask for details, though =).

    In the hopes it may help, I recently wrote myself a little tool that generates Docbook XML tables, with a little hacking and a little wrapping (ok, those last two "little"s are understatements) you could build this into a module that could prove very useful. It doesn't do constraints (yet), or map out procedures and the like. I have JDeveloper, a Java IDE that lets me browse the DB, and I haven't yet needed to add that functionality.

    This is pre-alpha code. Written after 10 minutes of thought. Is inefficient, and is certainly incomplete on the data types list. No warranty. Use at your own risk. I take no responsibility for melted CPUs or other damages to you, your love life, your career, etc. No license, though if I work it up into a module.

    update: I left out the bit of code where I got the table name -- I just nabbed it from the command line. To get a list of table names in Oracle, use:

    SELECT table_name FROM user_tables

    And get a list of constraints with

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

    First placeholder should be uc $username and second should be uc $table_name ... one place where Oracle *IS* case-sensitive. Also note that the view user_constraints is more likely to be truly helpful here.

    I'm continuing to work on it, at least in terms of generating XML, but there's no reason it couldn't be hacked to produce data structures and subclassed to generate whatever from those structures.

    #!/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 S +QL_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></e +ntry> </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;

    HTH, it's just a start.

    perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); +$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth +er_name\n"'
      Thank you, arturo. I cannot believe such a basic need for every lazy developer is not implemented yet. It looks like such a tool might be my first contribution to Open Source community...8^)

      I found interesting open source starting point, OracleTool, www.oracletool.com, which can generate CREATE statement with all the meat. I am just thinking about gutting some procedures from there and make them to generate half-decent HTML tables with important info.
      If you want to look into it to re-use it in your code, check procedure tableDDL(). It is in perl...8^)

      pmas
      To make errors is human. But to make million errors per second, you need a computer.

Re: Looking for a tool to print ORACLE schema as set of text or HTML pages.
by sevensven (Pilgrim) on Nov 09, 2001 at 22:35 UTC

    Sugestions ? why yes, I've one for you : RDBAL and RDBAL::Schema

    Links ? got two for you, here you go, RDBAL and RDBALL::Schema on CPAN

    RDBAL - Relational DataBase Abstraction Layer class (ReDBALl) is a very interesting module, that could even (and has been, trust me on that) be used to automate html input form creation. It will allow you the get exactly the information your looking for, without having to fidle with the RDBM that you happen to be using at the time.

    Here is what I'm using to get tables, stored procedures and stored procedures code out of the database (yes, there are more ways to get this info, but using perl and CPAN modules is just so sweet, you've got to love it ;-)

    use RDBAL; use RDBAL::Schema; use strict; # generic RDBALL::Connect # $X = RDBAL::Connect('username', 'password', 'server') or die ("cant +connect: $!"); # here i'm using ODBC to get to a MSSQL database my $X = RDBAL::Connect('user','password', 'dsn_name', 'ODBC', 'databas +e') or die ("cant connect: $!"); print "rdbal connected\n"; my $schema = new RDBAL::Schema ($X,'visao') or die ("cant make schema +for visao : $!"); my $database = $schema->Database() or die ("cant get database from sch +ema : $!"); my @user_tables = $schema->User_Tables() or die ("cant get user tables + from schema : $!"); my @procedures = $schema->Procedures() or die ("cant get procedures fr +om schema : $!"); foreach my $t (@user_tables) { print "[table] : $t\n"; } foreach my $p (@procedures) { print "[procedure] : $p\n"; my $comments = $schema->Comments($p); print "[procedure code] : $comments\n"; } print "that's all, folks.\n"

      Question: In your code, for an Oracle database, what would $database contain in the following code (the SID or DSN e.g): $schema = new RDBAL::Schema($X,$database, -get_system => 1);
Re: Looking for a tool to print ORACLE schema as set of text or HTML pages.
by Fastolfe (Vicar) on Nov 09, 2001 at 21:16 UTC
      How about DDL::Oracle? http://search.cpan.org/search?dist=DDL-Oracle
      Thank you, I've seen the one you suggested. DB_browser is good to view/update data, it is a good replacement to view your data via DataSheet view of M$ Access.

      However, it is not what I am looking for.

      I would like to run a program which will generate set of pages with just database schema info, tables/fieldnames, without any data. It can be done, it is a convenient way for a developer to look what columns are in what table. I just cannot believe it was not done before...

      I know this is not a perl question. Why I am asking here? Because perl will be my first tool of chioce to implement such a thing, and if it is partially done by somebody, to tinker it little more to do exactly what I want to.

      pmas
      To make errors is human. But to make million errors per second, you need a computer.

Re: Looking for a tool to print ORACLE schema as set of text or HTML pages.
by t'mo (Pilgrim) on Nov 09, 2001 at 22:16 UTC

    Can't you just give the DB an SQL query and let it tell you? E.g.,

    DESCRIBE tablename

    ...or, depending on the flavor of SQL used by the DB...

    DESCRIBE SELECT * FROM tablename

    and then print/format/munge/whatever the result?

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://124397]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2022-05-25 19:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (90 votes). Check out past polls.

    Notices?