/*******************************************************************************
 * File:        gen_validate.sql
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        28-Aug-03
 *
 * Description:
 *      SQL*Plus script to dump metadata about a schema, intended for comparison
 *	with the same schema in another database.  The purpose is to identify
 *	differences between the two schemas.
 *
 *	The script first dumps metadata from the data dictionary about objects
 *	within the schema.  This metadata is dumped into a "flat-file" which has
 *	variable-length data delimited by "~" (i.e. "tilde") characters.
 *
 *	Then, the script generates another SQL*Plus script which will be used
 *	to perform various validation measurements against all columns within
 *	the tables in the schemas.
 *
 *	The flat-files and the metadata they contain is:
 *
 *		Flat-file name			Contains
 *		==============			========
 *		<SCHEMA-NAME>_tab_validate.dat	column names and order from
 *						tables and views
 *		<SCHEMA-NAME>_ind_validate.dat	column names and order from
 *						indexes
 *		<SCHEMA-NAME>_seq_validate.dat	sequences names and the last
 *						value used
 *		<SCHEMA-NAME>_syn_validate.dat	synonym names
 *		<SCHEMA-NAME>_src_validate.dat	stored PL/SQL program module
 *						names, along with the number
 *						lines of code and the sum of
 *						characters
 *		<SCHEMA-NAME>_trg_validate.dat	trigger names and types
 *		<SCHEMA-NAME>_lnk_validate.dat	database link names and types
 *
 *	Validation measurements performed by the generated script include:
 *
 *		Measurement action		Comments
 *		==================		========
 *		count				COUNT(*), count of rows in table,
 *						performed once per table only
 *
 *		count-distinct			COUNT(DISTINCT <col-name>)
 *						for each column, to count the
 *						number of non-NULL values for
 *						the column in the table
 *
 *		sum-strlen			SUM(LENGTH(<col-name>) to
 *						determine the total length of
 *						all columns defined in one of
 *						the string datatypes (i.e.
 *						CHAR, NCHAR, VARCHAR2,
 *						NVARCHAR2, CLOB, NCLOB)
 *
 *		avg-strlen			AVG(LENGTH(<col-name>) to
 *						determine the average length of
 *						all columns defined in one of
 *						the string datatypes (i.e.
 *						CHAR, NCHAR, VARCHAR2,
 *						NVARCHAR2, CLOB, NCLOB)
 *
 *		sum-date			SUM(TO_NUMBER(TO_CHAR(<col-name>,
 *						'YYYYMMDDHH24MISS'))) used to
 *						determine the total of DATE
 *						datatype columns when expressed
 *						as a 12-digit number
 *
 *		avg-date			AVG(TO_NUMBER(TO_CHAR(<col-name>,
 *						'YYYYMMDDHH24MISS'))) used to
 *						determine the average of DATE
 *						datatype columns when expressed
 *						as a 12-digit number
 *
 *		sum-number			SUM(<col-name>) to determine the
 *						summary total of a numeric
 *						column
 *
 *		avg-number			AVG(<col-name>) to determine the
 *						average of a numeric column
 *
 *	The ".dat" files generated by this script are intended to be loaded
 *	(using SQL*Loader) into a database table for comparison with similar
 *	data from another database.
 *
 *	The SQL*Loader control files will also be generated by this script.
 *	The destination table is defined as follows:
 *
 *	    create table data_validate
 *	    (
 *		db_name		 varchar2(30)	not null,
 *		dbid		 integer	not null,
 *		timestamp	 date		not null,
 *		type		 varchar2(30)	not null,
 *		owner		 varchar2(30)	not null,
 *		name		 varchar2(30)	not null,
 *		column_id	 integer	    null,
 *		column_name	 varchar2(30)	    null,
 *		last_number	 integer	    null,
 *		table_owner	 varchar2(30)	    null,
 *		table_name	 varchar2(30)	    null,
 *		db_link		 varchar2(30)	    null,
 *		row_count	 integer	    null,
 *		byte_count	 integer	    null,
 *		trigger_type	 varchar2(30)	    null,
 *		triggering_event varchar2(30)	    null,
 *		username	 varchar2(30)	    null,
 *		measure_type	 varchar2(30)	    null,
 *		value		 number		    null
 *	    )
 *
 *	To load the data from the ".dat" data files into this table, use the
 *	SQL*Loader utility as follows:
 *
 *		$ sqlldr control=toad_data_validate.ctl userid=<un>/<pwd>@<db>
 *
 *	The following UNIX "korn-shell" code may be helpful:
 *
 *		$ sqlplus <un>/<pwd>@<db> @gen_validate <schema-name>
 *		$ for _f in `ls -1 <schema-name>_*_validate.ctl`
 *		> do
 *		>     sqlldr control=${_f} userid=<un>/<pwd>@<db>
 *		> done
 *		$ grep "Rows not loaded" <schema-name>_*_validate.log
 *
 *
 * Modifications:  
 ******************************************************************************/
whenever oserror exit failure
whenever sqlerror exit failure
col sort0 noprint
col sort1 noprint
col sort2 noprint
col sort3 noprint
set echo off feedback off timing off pagesize 0 linesize 80 trimspool on trimout on recsep off verify off

define owner="&1"
col i_owner noprint new_value owner
select lower('&&owner') i_owner from dual;
col db_name noprint new_value db_name
col dbid noprint new_value dbid
select name db_name, dbid from v$database;

spool &&owner._tab_validate.dat
select	decode(t.table_name, '', 'VIEW', 'TABLE') sort0,
	c.owner sort1,
	c.table_name sort2,
	c.column_id sort3,
	decode(t.table_name,'','VIEW','TABLE')||'~'||c.owner||'~'||c.table_name||'~'||c.column_id||'~'||c.column_name txt
from	dba_tab_columns	c,
	dba_tables	t
where	c.owner = upper('&&owner')
and	t.owner (+) = c.owner
and	t.table_name (+) = c.table_name
order by 1, 2, 3, 4;
spool off

spool &&owner._tab_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._tab_validate.dat'
prompt BADFILE '&&owner._tab_validate.bad'
prompt DISCARDFILE '&&owner._tab_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CHAR
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       COLUMN_ID        INTEGER EXTERNAL
prompt ,       COLUMN_NAME      CHAR
prompt )
spool off

spool &&owner._ind_validate.dat
select	index_owner sort0,
	index_name sort1,
	table_name sort2,
	column_position sort3,
	index_owner||'~'||index_name||'~'||table_owner||'~'||table_name||'~'||column_position||'~'||column_name txt
from	dba_ind_columns
where	table_owner = upper('&&owner')
order by 1, 2, 3, 4;
spool off

spool &&owner._ind_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._ind_validate.dat'
prompt BADFILE '&&owner._ind_validate.bad'
prompt DISCARDFILE '&&owner._ind_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'INDEX'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       TABLE_NAME       CHAR
prompt ,       TABLE_NAME       CHAR
prompt ,       COLUMN_ID        INTEGER EXTERNAL
prompt ,       COLUMN_NAME      CHAR
prompt )
spool off

spool &&owner._seq_validate.dat
select	sequence_owner sort0,
	sequence_name sort1,
	sequence_owner||'~'||sequence_name||'~'||last_number txt
from	dba_sequences
where	sequence_owner = upper('&&owner')
order by 1, 2;
spool off

spool &&owner._seq_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._seq_validate.dat'
prompt BADFILE '&&owner._seq_validate.bad'
prompt DISCARDFILE '&&owner._seq_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'SEQUENCE'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       LAST_NUMBER      INTEGER EXTERNAL
prompt )
spool off

spool &&owner._syn_validate.dat
select	owner sort0,
	synonym_name sort1,
	table_owner sort2,
	table_name sort3,
	owner||'~'||synonym_name||'~'||table_owner||'~'||table_name||'~'||db_link txt
from	dba_synonyms
where	owner = upper('&&owner')
order by 1, 2, 3, 4;
spool off

spool &&owner._syn_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._syn_validate.dat'
prompt BADFILE '&&owner._syn_validate.bad'
prompt DISCARDFILE '&&owner._syn_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'SYNONYM'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       TABLE_OWNER      CHAR
prompt ,       TABLE_NAME       CHAR
prompt ,       DB_LINK          CHAR
prompt )
spool off

spool &&owner._src_validate.dat
select	type sort0,
	owner sort1,
	name sort2,
	type||'~'||owner||'~'||name||'~'||count(*)||'~'||sum(length(text)) txt
from	dba_source
where	owner = upper('&&owner')
group by type, owner, name
order by 1, 2, 3;
spool off

spool &&owner._src_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._src_validate.dat'
prompt BADFILE '&&owner._src_validate.bad'
prompt DISCARDFILE '&&owner._src_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CHAR
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       ROW_COUNT        INTEGER EXTERNAL
prompt ,       BYTE_COUNT       INTEGER EXTERNAL
prompt )
spool off

spool &&owner._trg_validate.dat
select	owner sort0,
	trigger_name sort1,
	owner||'~'||trigger_name||'~'||trigger_type||'~'||triggering_event txt
from	dba_triggers
where	owner = upper('&&owner')
order by 1, 2, 3;
spool off

spool &&owner._trg_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._trg_validate.dat'
prompt BADFILE '&&owner._trg_validate.bad'
prompt DISCARDFILE '&&owner._trg_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'TRIGGER'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       TRIGGER_TYPE     CHAR
prompt ,       TRIGGERING_EVENT CHAR
prompt )
spool off

spool &&owner._lnk_validate.dat
select	owner sort0,
	db_link sort1,
	owner||'~'||db_link||'~'||username txt
from	dba_db_links
where	owner = upper('&&owner')
order by 1, 2, 3;
spool off

spool &&owner._lnk_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._lnk_validate.dat'
prompt BADFILE '&&owner._lnk_validate.bad'
prompt DISCARDFILE '&&owner._lnk_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'DATABASE LINK'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       USERNAME         CHAR
prompt )
spool off

set linesize 500

spool &&owner._data_validate.sql
prompt whenever oserror exit failure
prompt whenever sqlerror exit failure
prompt set echo off feedback off timing off pagesize 0 linesize 100 trimspool on trimout on recsep off
prompt
prompt spool &&owner._data_validate.dat
prompt
select	c.owner sort0,
	c.table_name sort1,
	0 sort2,
	c.column_id sort3,
	decode(c.column_id, 1, 'select', ',')||chr(9)||
	decode(c.data_type,
		'LONG', ''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~count-distinct-on-long~-1''',
		'LONG RAW', ''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~count-distinct-on-longraw~-1''',
			''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~count-distinct~''||count(distinct '||c.column_name||')')||
	decode(c.data_type,
	    'CHAR',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'NCHAR',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'VARCHAR2',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'NVARCHAR2',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'CLOB',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'NCLOB',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-strlen~''||nvl(sum(nvl(length('||c.column_name||'),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-strlen~''||trim(to_char(nvl(avg(nvl(length('||c.column_name||'),0)),0),''99999999999999999990.000''))',
	    'DATE',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-date~''||nvl(sum(nvl(to_number(to_char('||c.column_name||',''YYYYMMDDHH24MISS'')),0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-date~''||trim(to_char(nvl(avg(nvl(to_number(to_char('||c.column_name||',''YYYYMMDDHH24MISS'')),0)),0),''999999999999999999999999999990.000''))',
	    'NUMBER',
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~sum-number~''||nvl(sum(nvl('||c.column_name||',0)),0)'||
		chr(10)||','||chr(9)||''''||c.owner||'~'||c.table_name||'~'||c.column_name||'~avg-number~''||trim(to_char(nvl(avg(nvl('||c.column_name||',0)),0),''99999999999999999990.000''))') txt
from	dba_tab_columns	c,
	dba_tables	t
where	c.owner = t.owner
and	c.table_name = t.table_name
and	t.owner = upper('&&owner')
union
select	owner sort0,
	table_name sort1,
	1 sort2,
	0 sort3,
	','||chr(9)||''''||owner||'~'||table_name||'~*~count~''||count(*)'||chr(10)||
	'from'||chr(9)||owner||'.'||table_name||';'||chr(10)||chr(10) txt
from	dba_tables
where	owner = upper('&&owner')
order by 1, 2, 3, 4;
prompt exit success
spool off

spool &&owner._data_validate.ctl
prompt LOAD DATA
prompt INFILE '&&owner._data_validate.dat'
prompt BADFILE '&&owner._data_validate.bad'
prompt DISCARDFILE '&&owner._data_validate.dsc'
prompt INTO TABLE DATA_VALIDATE APPEND
prompt FIELDS TERMINATED BY '~'
prompt (       DB_NAME          CONSTANT '&&db_name'
prompt ,       DBID             CONSTANT &&dbid
prompt ,       TIMESTAMP        SYSDATE
prompt ,       TYPE             CONSTANT 'DATA'
prompt ,       OWNER            CHAR
prompt ,       NAME             CHAR
prompt ,       COLUMN_NAME      CHAR
prompt ,       MEASURE_TYPE     CHAR
prompt ,       VALUE
prompt )
spool off

REM 
REM ...running the script is commented out by default;  please uncomment if you prefer when you prefer...
REM 
start &&owner._data_validate

exit success