Compare column count with central objects for tables

Note that this also show correct usage of PROMPT and ACCEPT in SQL scripts.

--  Note that this script will create a temp table to
--  make the execution quicker

-- drop this table
PROMPT
PROMPT You're about to determine the differences in column counts between
PROMPT central objects and you're data data source
PROMPT
ACCEPT data_owner CHAR PROMPT 'Enter the OneWorld Data owner name - in caps -> '
ACCEPT pathcode_owner CHAR PROMPT 'Enter the OneWorld pathcode owner  -> '

set pagesize 50

drop table temp_table_count;

create table temp_table_count
(table_name varchar(50) not null,
spec_column_count integer not null,
relational_column_count integer not null)
;

-- insert the relational column counts
--
insert into temp_table_count
select table_name, 0, count(1)
from all_tab_columns
where owner = '&data_owner'
group by table_name
order by table_name ;

commit;

-- get the spec table counts from the F98711
--
update temp_table_count
set spec_column_count = (
select count(1)
from &pathcode_owner..f98711
where  ltrim(rtrim(tdobnm)) = table_name);

commit;

-- Compare and display the differences
--
select table_name || ' ' || spec_column_count || ' ' || relational_column_count
from temp_table_count
where spec_column_count != relational_column_count;