Generating SQL Code

The SQL code (not in the screen shot) will generate the code in the screen shot below for each user table in All_Tables. Basically, the table name, comment, row count, table description and sample data is shown for each.

That code in turn when run, will give you the report shown at the right. This is done this way because table names are constantly changing in the data dictionary.

Concatenating the ASCII character 10 will cause a line feed. Otherwise, all code would display on one line as SQL reports normally do.

--+--------------------------------------------------------------
--| When generating code, turn off the column headings and page breaks.
--+--------------------------------------------------------------

Column RowCnt Format 999,999,999
Set Heading &OnOff Newpage None Echo &OnOff Term &OnOff SqlBlankLines On

--+--------------------------------------------------------------
--| Spool to a file with a SQL extension
--+--------------------------------------------------------------
Spool Dumptbls.Sql

--+--------------------------------------------------------------
--| Draw a dividing line between entries and show the table name.
--+--------------------------------------------------------------

Select 'Set Heading Off Newpage None Pagesize 9999 Linesize 2000'
       || Chr(10) || 'Prompt'
       || Chr(10) || 'Prompt ======================================================================'
       || Chr(10) || 'Prompt'
       || Chr(10) || 'Prompt Table_Name: '||Table_Name 

--+--------------------------------------------------------------
--| Display a comment if there is one
--+--------------------------------------------------------------

       || Chr(10) || Case When Comments Is Not Null 
                          Then 'Prompt Comments:   '||Comments 
                          Else ' ' 
                     End
       || Chr(10) || 'Prompt'

--+--------------------------------------------------------------
--| Write the select that will generate the row count.
--+--------------------------------------------------------------

       || Chr(10) || 'Select ''Row Count:  ''||Count(*) RowCnt
                        From '||Table_Name|| ';'
       || Chr(10) || 'Prompt'

--+--------------------------------------------------------------
--| Show the file description
--+--------------------------------------------------------------

       || Chr(10) || 'Set Linesize 70'
       || Chr(10) || 'Desc '||Table_name

--+--------------------------------------------------------------
--| Turn on the column headings
--+--------------------------------------------------------------

       || Chr(10) || 'Set Linesize 200 Heading On Newpage None'

--+--------------------------------------------------------------
--| Print a few sample records from the table
--+--------------------------------------------------------------

       || Chr(10) || 'Select * 
                        From '||Table_Name|| ' 
                       Where Rownum < 5;'

  From All_Tables 
  Join All_Tab_Comments 
 Using (Table_Name, Owner)
 Where Owner = '&_User'
and table_name in ('SALGRADE','DEPT')
  Order by Table_Name;

Spool Off

--+--------------------------------------------------------------
--| Set to display the final report
--+--------------------------------------------------------------
Set Term On
--+--------------------------------------------------------------
--| Spool file and run the code just spooled above.
--+--------------------------------------------------------------

Spool Dumptbls.Txt
@Dumptbls
Spool Off
Prompt 
Prompt Note: Your table data is in DumpTbls.Txt.
Prompt ====