www .Just SQL. com  

Script Name :

Description :

a site for the SQL*Plus specialist

Site Map


Count n tables and store the result in a results table.

-- This is a 3-Level script, and is an example of a more complex
-- embedded script. It was presented at UK-OUG 2000, but from
-- subsequent emails, it is apparent that the construction
-- requires a little additional explanation.
-- This example presents the 3-Level solution, together with
-- the output of this first script (a Level-2 script), and
-- then finally the third and completing Level-1 script.
-- Examine the transitions carefully to see how the code works.
-- The sequence of events breaks down thus:
-- 1) identify tables to have their rows counted. They are
-- named in a reference table (the 'driver' table) with their owner.
-- 2) count those rows.
-- 3) update the 'driver' table with the row counts just obtained.
-- The 'driver' table is called MONITOR_TAB. Fields are OWNER,
-- Set your SQL*Plus environment settings to choice. The
-- following are recommended:
set feedback off
set pagesize 0
set echo off
set trims on
set termout off
set sqlprompt ''
-- This is the complete Level-3 script. Its first task is to
-- identify the tables to be counted by reference to the 'driver'
-- table.
spool a.sql
 ' ''update monitor_tab set the_count='' ||count(*)||' || chr(10) ||
 ' '',the_date=sysdate '' || chr(10) ||' || chr(10) ||
 ' '' where owner= '' ||'''''''||OWNER||'''''''||' || chr(10) ||
 ' '' and tname= '' ||'''''''||TNAME||''''''''||'||'||''';''' || chr(10) ||
 'from '||owner||'.'||tname||';'
from monitor_tab;
spool off
spool b.sql
-- Running script 'a.sql' produces the Level-2 script, which
-- contains the Owner and Table_Name of each table to be counted.
-- There will be as many Level-2 scripts generated as there
-- are tables to be counted.
spool off
set feedback on
set pagesize 24
set echo on
set termout on
set sqlprompt 'SQL>'
-- Running script 'b.sql' produces the final Level-1 updated script.
-- There will be as many update scripts produced as there are to count.
--End of Script Function. Descriptions follow:
-- This is the output of the above Level-3 script. It is a Level-2 script,
-- which obtains the actual table row counts of the table names identified
-- from the previous script.
-- select
-- 'update monitor_tab set the_count=' ||count(*)||
-- ',the_date=sysdate ' || chr(10) ||
-- ' where owner= ' ||'''DEMO'''||
-- ' and tname= ' ||'''STAFF'''||';'
-- from DEMO.STAFF;
-- This is the output of the above Level-2 script. It is the final Level-1
-- script which actions the Update of each row with the counts obtained from
-- the Level-2 script, eg:
-- update monitor_tab set the_count=765,the_date=sysdate
-- where owner= 'DEMO' and tname= 'STAFF';
-- end

[Home] [Inspiration] [About this Site] [Scripts Intro] [Publications]

e-Mail Connection