+1 Software Engineering®
  Home    Free Coffee    Tech Stuff    Contact Us

+1DataDictionaryTM Scripts

April 2016

+1 Software Engineering LLC®
2510-G Las Posas Road, Suite 438
Camarillo, California 93010
805-586-3445

Table of Contents

1. Introduction
1.1 indexes.txt 1.1.1 Description 1.1.2 Format 1.1.3 i.sql 1.1.4 Example
1.2 tables.txt 1.2.1 Description 1.2.2 Format 1.2.3 t.sql 1.2.4 Example
1.3 views.txt 1.3.1 Description 1.3.2 Format 1.3.3 v.sql 1.3.4 Example
2. Run +1DataDictionary
2.1 run_de.sh Shell Script
2.2 Generated index.html
3. +1 Software Engineering LLC Support

 


+1DataDictionaryTM Scripts

1. Introduction

+1DataDictionary provides a description of one or more databases without having to log into each database and run "desc" commands or run SQL queries. You can answer database questions during a meeting when +1DataDictionary generated HTML output is stored on your laptop. When the information is stored on an internal web site, the information is accessible to everyone on a project without requiring everyone to have access to the database. The run_de.sh shell script, discussed later in this document, can be used to generate up-to-date HTML documentation for one or more Oracle databases automatically or on demand.

To generate the HTML output for an existing database, the first step is to run the i.sql, t.sql, and v.sql scripts. These scripts generate the indexes.txt, tables.txt, and views.txt files respectively.

For tables, +1DataDictionary reads either the tables.txt file as input or it can read in the create_tables.txt file created by +1Copybook instead. For indexes and views, the indexes.txt and views.txt files are created only by running the i.sql and v.sql scripts.

+1Copybook is used to generate the CREATE TABLE scripts based on COBOL copybooks. +1Copybook automatically calls +1DataDictionary to generate the HTML output. After you have run the CREATE TABLE scripts generated by +1Copybook to create the actual database tables, +1DataDictionary should be used from that point on to document any changes which might occur to the indexes, tables, and views for the database.

If the database tables exist, you should run the t.sql script to generate the tables.txt file.

Unfortunately Oracle does not support a way to turn off the number of "rows selected" trailer from printing when running an SQL select command. After generating the indexes.txt, tables.txt, and views.txt files, you must delete the last line showing the "rows selected" in these files. If you don't and you do run +1DataDictionary, you will see a numerically named table in the generated HTML output. If this does happen, simply edit the .txt files to remove the "rows selected" lines, and run +1DataDictionary again.

If running +1DataDictionary as a cron job or to remove the line using a Unix command before running +1DataDictionary, you can run:

    % grep -v "rows selected" tables.txt > temp; mv temp tables.txt

which will remove the "rows selected" line from the tables.txt file.

The SQL scripts and generated output described in this document are for Oracle 10g. SQL scripts and generated output for Oracle 9i are different. +1 Software Engineering LLC will provide you with SQL scripts for both Oracle 9i and Oracle 10g.

1.1 indexes.txt

1.1.1 Description

File indexes.txt is a generated file and lists the table name, column position, column name, and index name. To generate the indexes.txt file, the i.sql script is used.

1.1.2 Format

Table NameColumn PositionColumn NameIndex Name:

Blank lines are allowed in the indexes.txt file.

1.1.3 i.sql 

/*
   Name:        i.sql
   Description: Selects the table_name, column_position, column_name, and
                index name from user_ind_columns.  This output is read in 
                by +1DataDictionary to generate the index descriptions.
   Output:      indexes.txt
   Version:     Oracle 10g, Version A
   Copyright:   (C) Copyright 2016 +1 Software Engineering LLC - All Rights Reserved
*/

spool indexes.txt

set pagesize 50000
set linesize 120
set heading off

break on table_name skip 2

column table_name format a30
column column_name format a30

select i.table_name, 
       i.column_position, 
       i.column_name, 
       i.index_name, 
       ':'
from user_ind_columns i, user_tables
where i.table_name = user_tables.table_name and 
      user_tables.dropped = 'NO'
order by table_name, index_name, column_position;

spool off

i.sql Script

After you run the i.sql script, you must edit the indexes.txt file and delete the "rows selected" line at the end of the file.

1.1.4 Example 

BILLING                       1 COMPANY_ID                     SYS_C005379          :

COMPANY                       1 COMPANY_ID                     SYS_C005383          :

TUTORIAL                      1 AREA_ID                        TUTORIAL_INDEX       :
                              2 TUTORIAL_ID                    TUTORIAL_INDEX       :

1.2 tables.txt

1.2.1 Description

File tables.txt is a generated file and lists the table name, column name, nullable, data type, length, precision, and scale. If the column data type is a NUMBER, the precision and scale are specified. To generate the tables.txt file, the t.sql script is used.

1.2.2 Format

Table NameColumn NameNullableData TypeLengthPrecisionScale:

Blank lines are allowed in the tables.txt file.

1.2.3 t.sql 

/*
   Name:        t.sql
   Description: Selects the table_name, column_name, nullable, data_type, 
                data_length, data_precision, and data_scale from user_tab_columns.
                This output is read in by +1DataDictionary to generate the table
                descriptions.
   Note:        After running this script, users must manually use an editor,
                such as vi, to remove the "number of rows" returned message
                located at the bottom of the tables.txt file.  Oracle does 
                not support a way to suppress this output.
   Output:      tables.txt
   Version:     Oracle 10g, Version A
   Copyright:   (C) Copyright 2016 +1 Software Engineering LLC - All Rights Reserved
*/

spool tables.txt

set pagesize 50000
set linesize 120
set heading off

break on table_name skip 2

column table_name format a30
column column_name format a30
column data_type format a15
column data_length format 9999
column data_precision format 9999
column data_scale format 9999

select user_tables.table_name, 
       column_name, 
       nullable,
       data_type, 
       data_length length, 
       data_precision precision, 
       data_scale scale, 
       ':'
from user_tab_columns, user_tables
where user_tab_columns.table_name = user_tables.table_name and 
      dropped = 'NO'
order by table_name, column_id;

spool off

t.sql Script

After you run the t.sql script, you must edit the tables.txt file and delete the "rows selected" line at the end of the file.

1.2.4 Example 

ADDRESS         COMPANY_ID                N VARCHAR2           25             :      
                ADDRESS                   Y VARCHAR2           40             :      
                ADDRESS2                  Y VARCHAR2           40             :      
                CITY                      Y VARCHAR2           40             :      
                STATE                     Y VARCHAR2           40             :      
                ZIP                       Y VARCHAR2           40             :      
                COUNTRY                   Y VARCHAR2           40             :      
                PHONE                     Y VARCHAR2           40             :      
                FAX                       Y VARCHAR2           40             :      

ADS             AREA_ID                   N VARCHAR2           25             :      
                AD_LOCATION               Y VARCHAR2           25             :      
                AD_SIZE                   Y VARCHAR2           25             :      
                NAME                      Y VARCHAR2          100             :      
                GIF                       Y VARCHAR2          100             :      
                URL                       Y VARCHAR2          100             :      
                COMPANY_ID                Y VARCHAR2           25             :      
                START_DATE                Y DATE                7             :      
                END_DATE                  Y DATE                7             :      
                DESCRIPTION               Y VARCHAR2          250             :      
                LAST_UPDATE               Y DATE                7             :      

BILLING         COMPANY_ID                N VARCHAR2           25             :      
                LAST_NAME                 N VARCHAR2           30             :      
                FIRST_NAME                N VARCHAR2           30             :      
                TITLE                     Y VARCHAR2           60             :      
                ADDRESS                   Y VARCHAR2           40             :      
                ADDRESS2                  Y VARCHAR2           40             :      
                CITY                      Y VARCHAR2           40             :      
                STATE                     Y VARCHAR2           40             :      
                ZIP                       Y VARCHAR2           40             :      
                COUNTRY                   Y VARCHAR2           40             :      
                PHONE                     Y VARCHAR2           40             :      
                FAX                       Y VARCHAR2           40             :      
                EMAIL                     Y VARCHAR2           80             :      
                DUE_DATE                  Y DATE                7             :      
                AMOUNT_DUE                Y NUMBER             22     10    2 :      
                FREQUENCY                 Y CHAR                1             :      
                LAST_UPDATE               Y DATE                7             :      
                DESCRIPTION               Y VARCHAR2          250             :      
                COMMENTS                  Y VARCHAR2          250             :      
                TOTAL_AMOUNT_DUE          Y NUMBER             22     10    2 :      

COMPANY         COMPANY_ID                N VARCHAR2           25             :      
                SHORT_NAME                N VARCHAR2           40             :      
                NAME                      N VARCHAR2           80             :      
                URL                       Y VARCHAR2          100             :      
                SE_SUPPORTER              Y CHAR                1             :      
                INFO_URL                  Y VARCHAR2          100             :      
                SALES_URL                 Y VARCHAR2          100             :      
                SUPPORT_URL               Y VARCHAR2          100             :      
                LAST_UPDATE               Y DATE                7             :      
                LOGO_FILE                 Y VARCHAR2           80             :      
                DESCRIPTION               Y VARCHAR2          250             :      
.
.
.

TUTORIAL        AREA_ID                   N VARCHAR2           25             :      
                TUTORIAL_ID               Y VARCHAR2           25             :      
                SHORT_DESC                Y VARCHAR2          100             :      
                NAME                      Y VARCHAR2          100             :      
                URL                       N VARCHAR2          100             :      
                AUTHOR                    Y VARCHAR2          100             :      
                DESCRIPTION               Y VARCHAR2          250             :

1.3 views.txt

1.3.1 Description

File views.txt is a generated file and lists the view name, column name, data type, length, precision, and scale. If the column data type is a NUMBER, the precision and scale are specified. To generate the views.txt file, the v.sql script is used.

1.3.2 Format

View NameColumn NameData TypeLengthPrecisionScale:

Blank lines are allowed in the views.txt file.

1.3.3 v.sql

To generate the views.txt file, run the following v.sql script. 

/*
   Name:        v.sql
   Description: Selects the view_name, column_name, data_type, data_length,
                data_precision, and data_scale from user_tab_columns. This output
                is read in by +1DataDictionary to generate the view descriptions.
   Note:        After running this script, users must manually use an editor,
                such as vi, to remove the "number of rows" returned message
                located at the bottom of the views.txt file.  Oracle does 
                not support a way to suppress this output.
   Output:      views.txt
   Version:     Oracle 10g, Version A
   Copyright:   (C) Copyright 2016 +1 Software Engineering LLC - All Rights Reserved
*/

spool views.txt

set echo off
set head off
set pagesize 50000
set linesize 120

break on view_name skip 2

column view_name format a30
column column_name format a30
column data_type format a8
column data_length format 9999
column data_precision format 9999

select view_name, 
       column_name, 
       data_type, 
       data_length length, 
       data_precision precision, 
       data_scale scale, 
       ':'
from user_tab_columns, user_views
where user_tab_columns.table_name = user_views.view_name
order by view_name, column_id;

spool off

v.sql Script

After you run the v.sql script, you must edit the views.txt file and delete the "rows selected" line at the end of the file.

1.3.4 Example 

COMPANY_VIEW         COMPANY_ID                VARCHAR2         25             :               
                     NAME                      VARCHAR2         80             :               
                     URL                       VARCHAR2        100             :               

TUTORIAL_VIEW        AREA_ID                   VARCHAR2         25             :               
                     TUTORIAL_ID               VARCHAR2         25             :               
                     NAME                      VARCHAR2        100             :

2. Run +1DataDictionary

After creating the indexes.txt, tables.txt, and views.txt files, you are ready to run the +1DataDictionary program to generate the HTML output for a database.

To generate output for more than one database:

  1. Change the ORACLE_SID stored in the database_id.txt file,

  2. Run the i.sql, t.sql, and v.sql scripts again to generate the indexes.txt, tables.txt, and views.txt files for the database,

  3. Run +1DataDictionary.

  4. Repeat above steps for each database.

The database.txt and index.txt files are described in the +1DataDictionary Users Guide. If you do not change the ORACLE_SID value using the database_id.txt file, then the default ORACLE_SID defined by your license or the ORACLE_SID defined in an existing database_id.txt file will be used and will overwrite the previously generated database information.

2.1 run_de.sh Shell Script

+1DataDictionary can be run as a standalone script or as a cron job. A cron job allows you to schedule how often the +1DataDictionary HTML output should be updated.

Below is the run_de.sh script which generates HTML output for three Oracle databases. By using the "set echo off" and "set term off" in sqlplus and by using the "-w" silent option when calling +1DataDictionary, there is no stdout output generated when this script runs as a standalone shell script or as a cron job.

To run this script as a Unix command, type: 

    % run_de.sh

To run this script as a cron job, two sample crontab entries are: 

    * 8,10,12,2,4,6 * * * /oracle/run_de.sh
* 4 * * * /oracle/run_de.sh

The first cron job entry runs run_de.sh at 8 AM, 10 AM, noon, 2 PM, 4 PM, and 6 PM everyday. The second cron job entry runs run_de.sh at 4 AM every morning. 

#!/bin/sh
#
#  Name:	  run_de.sh
#
#  Description:   This script generates +1DataDictionary output for three databases
#                 whose ORACLE_SIDs are: TEST10, TEST, and MY9204.  
#
#                 The /www/docs/databases/index.txt file looks like:
#
#                     TITLE     Production Databases
#                     TEST10    SoftwareEngineering.info's Database On Oracle 10g
#                     TEST      SoftwareEngineering.info's Database On Oracle 9.0.1
#                     MY9204    Scott's Database On Oracle 9.2.0.4
#                     HEADER    +1 Software Engineering LLC Documentation
#                     LINK      <a href=http://plus-one.com/de_users_guide.html>
#                               +1DataDictionary User's Guide</a>
#
#  To Run On A
#  Command Line:  Type: 
#                    % run_de.sh
#
#  To Run On A
#  Command Line
#  In Debug Mode: Type:
#                    % sh -x run_de.sh
#
#  To Run As A
#  Cron Job:      To add crontab entry, type:
#                    % crontab -e
#                 Using the vi editor, add the following line:
#                    00 8,12,16,20 * * *  /oracle/run_de.sh
#                 In this example, the above entry updates the +1DataDictionary HTML files 
#                 at 8 AM, noon, 4 PM, and 10 PM.  You can run run_de.sh at any 
#                 time intervals you like.
#                    Exit vi editor.  This enables the cron job.
#                 To see new crontab entry, type:
#                    % crontab -l
#
#  Output:        Updates HTML files found in the /www/docs/databases directory.
#
#  Version:       Version A
#
#  Copyright:     (C) Copyright 2016 +1 Software Engineering LLC - All Rights Reserved

#
#  Create the HTML outputs in /www/docs/databases.
#

cd /www/docs/databases

#
#  Oracle 10g Database
#

. /oracle/set10

sqlplus -s plusone/password << EOF
set echo off
set term off
@/oracle/10g/t.sql
@/oracle/10g/i.sql
@/oracle/10g/v.sql
EOF

grep -v "rows selected" tables.txt > temp; mv temp tables.txt
grep -v "rows selected" indexes.txt > temp; mv temp indexes.txt
grep -v "rows selected" views.txt > temp; mv temp views.txt

cp database_id.TEST10 database_id.txt

de -w

#
#  Oracle 9.0.1 Database
#

. /oracle/set901

sqlplus -s plusone/password << EOF
set echo off
set term off
@/oracle/9i/t.sql
@/oracle/9i/i.sql
@/oracle/9i/v.sql
EOF

grep -v "rows selected" tables.txt > temp; mv temp tables.txt
grep -v "rows selected" indexes.txt > temp; mv temp indexes.txt
grep -v "rows selected" views.txt > temp; mv temp views.txt

cp database_id.TEST database_id.txt

de -w

#
#  Oracle 9.2.0.4 Database
#

. /oracle/set9204

sqlplus -s scott/java123 << EOF
set echo off
set term off
@/oracle/9i/t.sql
@/oracle/9i/i.sql
@/oracle/9i/v.sql
EOF

grep -v "rows selected" tables.txt > temp; mv temp tables.txt
grep -v "rows selected" indexes.txt > temp; mv temp indexes.txt
grep -v "rows selected" views.txt > temp; mv temp views.txt

cp database_id.MY9204 database_id.txt

de -w

run_de.sh Shell Script

2.2 Generated index.html

The /www/docs/databases/html/index.html file generated by running run_de.sh follows:

Production Databases
Last Updated: 18-MAY-2016

SoftwareEngineering.info's Database On Oracle 10g

  1. TEST10 Tables
  2. TEST10 Indexes
  3. TEST10 Views

SoftwareEngineering.info's Database On Oracle 9.0.1

  1. TEST Tables
  2. TEST Indexes
  3. TEST Views

Scott's Database On Oracle 9.2.0.4

  1. MY9204 Tables
  2. MY9204 Indexes
  3. MY9204 Views
+1 Software Engineering LLC Documentation
  1. +1DataDictionary User's Guide

Output Generated By +1DataDictionary From +1 Software Engineering LLC.
Created on: 18-MAY-2016 4:00 AM

/www/docs/databases/html/index.html File

3. +1 Software Engineering LLC Support

For technical support, call:

    805-586-3445
    Monday-Friday
    8 AM-5 PM PST

Using technical support, you can:

  1. Discuss problems or ask questions to knowledgeable support personnel.
  2. Receive updates and releases for products originally purchased.
  3. Access customer-only documentation, such product presentations, white papers, and user manuals, on our web site.

All products include one year of free technical support. Extended support hours can be arranged.

To Top

+1DataDictionary Scripts, April 2016

+1Copybook and +1DataDictionary are trademarks of +1 Software Engineering LLC.

+1 Software Engineering is a registered trademark of +1 Software Engineering LLC.

Oracle, SQL*Loader, and all Oracle-based trademarks are trademarks or registered trademarks of of Oracle Corporation in the United States or other countries. +1 Software Engineering is independent of Oracle Corporation.

SoftwareEngineering.info is a trademark of SoftwareEngineering.info.

All other product names are trademarks of their respective holders.