Re: SQL*Plus

In article sle_at_spock.dis.cccd.edu, paulk_at_spock.dis.cccd.edu (Paul Krikorian) writes:
>
> Hello everyone,
> I have another SQL*Plus question. We are in the middle of our first
> Oracle project and the database is changed occasionally. I like to work
> from a printed copy of all the tables while I'm writing my PRO*COBOL
> program. The 'DESCRIBE' command in SQL*Plus works great for detailing one
> table, but since our database has over 40 tables, it is a pain printing
> all the tables at once.
>
> I would like to create an SQL*Plus script that would use the data
> stored in the system table USER_TAB_COLUMNS to print a report in the
> following format:
>
> Table Column Null? Type
> -------------------- ------------------------------- -------- ----
> table_1 column_1 NOT NULL NUMBER
> column_2 DATE
> column_3 NUMBER
> column_4 CHAR(x)
>
> table_2 column_1 CHAR(x)
> .
> .
> .
>
> table_n column_1 CHAR(x)
>
>
> I started writing the script and got part of it (not much), but
> decided to be lazy and just ask the net if there was an SQL*Plus command
> for this or if anybody else knows how to accomplish it.
>
> Places where I'm stuck are:
> (1) how to suppress TABLE_NAME after first occurrence of table
> (2) how to get the column NULLABLE to display as blanks when = 'Y'
> and as 'NOT NULL' when = 'N'
> (3) how to suppress DATA_LENGTH for data types like DATE or NUMBER
> (4) how to skip a line between tables
>
> Sorry if some of these things are simple, but I don't have much time
> to experiment with SQL*Plus.

Paul,

I have done something like this and found the easiest way (in UNIX) was to pipe the output of your SELECT statement to an awk or perl script and manipulate the output formatting that way.

Now that I've started using Oraperl, this sort of thing becomes even simpler to do. Oraperl is Kevin Stock's Perl patch that uses OCI to directly connect to the database. It's a great tool for data extraction and reporting!

---
___________________________________________________________________________

Jeff.Stander_at_ml.csiro.au        _--_|\        Database Analyst
CSIRO Division Of Fisheries    /      \       Pelagic Fisheries Resources
GPO Box 1538, Hobart           \_.--._/       Tasmania 7001, Australia
Aus Tel: 002-325-332                 v        Intl Tel: +61-02-325-332
Aus Fax: 002-325-000                          Intl Fax: +61-02-325-000
___________________________________________________________________________