--
-- COUNT.SQL
--
-- 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,
-- TNAME, THE_COUNT, THE_DATE.
--
-- 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
--
select
'select',
' ''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.
@a.sql
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.
@b.sql
--
--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
-- |
