Free Web Hosting by Netfirms
 Web Hosting by Netfirms | Free Domain Names by Netfirms

Home PageOracle SQL*/PLSQL Examples

 

Creates SQL script which can be used to create all of the referential integrity constraints for all on-SYS-owned tables.
crconstr.sql

Bugs fixed:
1) Fix errors in length calculations.
2) Fix syntax error for disabled check
constraints.
3) Fix brkline function.
4) Include Primary key constraint names.
Enhancements:
1) Support Oracle8 constraint syntax.
2) Add trimspool to reduce spool file size.
3) Add tblsize_schema parameter, to support
the constraint syntax required for the
tblsize.sql script.
4) Reformat cursors and variables for
standard template.
5) Add additional error traps.
6) Renamed temp report table.
7) Renamed variables.
8) Support freelists parameter.
9) Add "set define off" to created script.
10) Handle maxextents unlimited.
11) Add SPOOL statements in created file.
12) Include SYS%-named constraints.
 
rem $DBA/crconstr.sql
rem
rem Creates SQL script which can be used to create all of the referential
rem integrity constraints for all non-SYS-owned tables
rem
rem Restrictions:
rem	1) This script has been tested for Oracle 7, 8.0.3, and 8.0.4.
rem	2) The DBMS_OUTPUT and DBMS_SQL packages must exist and be executable by
rem	   the current username.  (Requires Oracle 7.2.2.3 or later, since
rem	   PL/SQL 2.2+ supports dynamic SQL).
rem	3) This script must be run by a user with the DBA role under Oracle 7.2
rem	   or later.  (Otherwise the FREELISTS and FREELIST GROUPS clauses will
rem	   have to be omitted).
rem	4) If running on Oracle 7.2, you must comment out the below
rem	   "set trimspool on" line, since it is supported as of Oracle 7.3 and
rem	   later.
rem	5) Objects are not currently supported.
rem
rem Last Change 08/13/98 by Brian Lomasky to support Oracle8, add trimspool,
rem					support constraints for tblsize.sql,
rem					reformat cursors and variables, add
rem					more error traps, fix syntax error for
rem					disabled check constraints, renamed
rem					temp report table, renamed variables,
rem					support freelists, fix brkline function,
rem					add "set define off" to created script.
rem Last Change 06/09/98 by Brian Lomasky to handle maxextents unlimited
rem Last Change 01/21/98 by Brian Lomasky to add SPOOL statements in created
rem   file, fix brkline function when trying to indent a line past 80 columns.
rem Last Change 08/13/97 by Brian Lomasky to include Primary key constraint
rem   names.
rem Last Change 06/30/97 by Brian Lomasky to include SYS%-named constraints.
rem Last Change 10/18/96 by Brian Lomasky to fix brkline function for an
rem   incorrectly-calculated wrap position if a break occurs right after a
rem   semicolon.
rem Last Change 10/16/96 by Brian Lomasky to include status (if disabled) and
rem   include the index specification for unique constraints
rem Last Change 10/14/96 by Brian Lomasky to include search_condition associated
rem   with a table or column CHECK(condition) constraint
rem Last Change 10/11/96 by Brian Lomasky to ensure owner.table does not wrap
rem   and to use data dictionary data types
rem Last Change 09/25/96 by Brian Lomasky to create all primary key and unique
rem   constraints before any referential integrity constraints
rem Last Change 09/04/96 by Brian Lomasky to rename script file, remove
rem   quotes around table names, use K or M in initial and next extents (if
rem   possible)
rem Last Change 09/03/96 by Brian Lomasky to fix quotes around names.  Added
rem     quotes around owners and table names
rem Created 07/30/96 by Brian Lomasky.
rem
rem *************************************************************************
rem * if tblsize_schema is set to '*', all constraints will be printed.     *
rem * if tblsize_schema is set to a specific schema name:                   *
rem *   1) Only the constraints for the given schema, or where they are     *
rem *      dependent on the specified schema will be printed.               *
rem *   2) No Primary or Unique Key constraints will be printed.            *
rem *   3) No NOT NULL constraints will be printed.                         *
rem *   (This supports the case when tblsize.sql is being executed)         *
rem *************************************************************************
def tblsize_schema='*'
set feedback off
set heading off 
set termout off
set echo off
set pagesize 0
set newpage 0
set linesize 80
set recsep off
set verify off 
rem Cleanup
set termout on
select 'Creating constraint build script...' from dual;
rem Create temp table for output report
create table crconstr_det_tmp (
	linen		number,
	lin		varchar2(80));
def q=chr(39)
set serveroutput on size 1000000
declare
	cursor ora8_cursor is select
		table_name
		from sys.dba_tables
		where owner = 'SYS' and table_name = 'TABPART$';
	cursor param_cursor is select
		value
		from v$parameter
		where name = 'db_block_size';
	cursor cons_cursor is
		select	a.owner,
			a.table_name,
			a.constraint_name,
			a.constraint_type,
			a.search_condition,
			b.table_name r_table,
			a.r_owner,
			a.r_constraint_name,
			a.delete_rule,
			a.status
		from	sys.dba_constraints a, sys.dba_constraints b
		where	a.r_constraint_name = b.constraint_name (+) and
				a.owner <> 'SYS' and ('&tblsize_schema' = '*'
				or a.owner = '&tblsize_schema' or
				a.r_owner = '&tblsize_schema') and
				('&tblsize_schema' = '*' or
				a.constraint_type <> 'P' and
				a.constraint_type <> 'U')
		order by
			decode(a.constraint_type, 'P', 0, 'U', 0, 'R', 1, 2),
			a.owner, a.table_name, a.constraint_name,
			decode(a.constraint_type, 'P', 0, 1);
	cursor cons_cols_cursor (my_owner in varchar2,
		my_constraint_name in varchar2, my_table_name in varchar2) is
		select column_name, position
		from sys.dba_cons_columns
		where owner = my_owner and
			constraint_name = my_constraint_name and
			table_name = my_table_name
		order by position;
	cursor cons_ref_cursor (my_owner in varchar2, my_cons_name in varchar2)
		is
		select table_name
		from sys.dba_cons_columns
		where owner = my_owner and constraint_name = my_cons_name
	   	order by position;
	ora8			boolean;
	db_block_size		number;
	cursor1			integer;
	cursor2			integer;
	stmnt			varchar2(2000);
	rows_processed		number;
	lv_owner		sys.dba_constraints.owner%TYPE;
	lv_constraint_name	sys.dba_constraints.constraint_name%TYPE;
	lv_constraint_type	sys.dba_constraints.constraint_type%TYPE;
	lv_table_name		sys.dba_constraints.table_name%TYPE;
	lv_search_condition	sys.dba_constraints.search_condition%TYPE;
	lv_r_owner		sys.dba_constraints.r_owner%TYPE;
	lv_r_table_name		sys.dba_constraints.table_name%TYPE;
	lv_r_constraint_name	sys.dba_constraints.r_constraint_name%TYPE;
	lv_delete_rule		sys.dba_constraints.delete_rule%TYPE;
	lv_status		sys.dba_constraints.status%TYPE;
	lv_deferrable		varchar2(14);
	lv_deferred		varchar2(9);
	lv_validated		varchar2(13);
	lv_cons_ref_table_name	sys.dba_cons_columns.table_name%TYPE;
	lv_column_name		sys.dba_cons_columns.column_name%TYPE;
	lv_position		sys.dba_cons_columns.position%TYPE;
	lv_tablespace_name	sys.dba_indexes.tablespace_name%TYPE;
	lv_ini_trans		sys.dba_indexes.ini_trans%TYPE;
	lv_max_trans		sys.dba_indexes.max_trans%TYPE;
	lv_initial_extent	sys.dba_indexes.initial_extent%TYPE;
	lv_next_extent		sys.dba_indexes.next_extent%TYPE;
	lv_min_extents		sys.dba_indexes.min_extents%TYPE;
	lv_max_extents		sys.dba_indexes.max_extents%TYPE;
	lv_pct_increase		sys.dba_indexes.pct_increase%TYPE;
	lv_pct_free		sys.dba_indexes.pct_free%TYPE;
	lv_freelists		sys.dba_indexes.freelists%TYPE;
	lv_freelist_groups	sys.dba_indexes.freelist_groups%TYPE;
	lv_logging		varchar2(9);
	lv_partitioned		varchar2(3);
	lv_buffer_pool		varchar2(7);
	found_index		boolean;
	lineno			number;
	pending			boolean;
	nn_pos			number;
	columns_reqd		boolean;
	initial_extent_size	varchar2(16);
	next_extent_size	varchar2(16);
	text_length		number;
	startp			number;
	xchar			number;
	break_pos		number;
	last_break		number;
	lf_pos			number;
	semi_pos		number;
	backwards		number;
	offset			number;
	out_start		number;
	real_len		number;
	l			number;
	bef_chars		varchar2(2000);
	a_lin			varchar2(2000);
	my_lin			varchar2(2000);
	no_ref_cons		exception;
	function wri(x_lin in varchar2, x_str in varchar2, x_force in number)
		return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lineno := lineno + 1;
			insert into crconstr_det_tmp values (lineno, x_lin);
			if x_force = 0
			then
				return '    '||x_str;
			else
				lineno := lineno + 1;
				if substr(x_lin,1,2) = '  '
				then
					insert into crconstr_det_tmp values (
						lineno, x_str);
				else
					insert into crconstr_det_tmp values (
						lineno, '    '||x_str);
				end if;
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lineno := lineno + 1;
				insert into crconstr_det_tmp values (
					lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;
	function brkline(x_lin in varchar2, x_str in varchar2) return varchar2
	is
	begin
		my_lin := x_lin;
		text_length := nvl(length(x_str), 0);
		-- Break long text field into smaller groups to print
		if text_length = 0 then
			return x_lin;
		end if;
		startp := 1;
		while startp <= text_length
		loop
			--
			-- See if line begins with a --
			--
			-- Calculate real length of line that is <= 80 chars
			-- (taking into account that each tab will move to the
			-- next multiple of 8)
			offset := 0;
			real_len := 0;
			out_start := startp;
			while out_start <= text_length
			loop
				if substr(x_str, out_start, 1) = chr(9) then
					l := floor(real_len / 8) * 8 + 8 -
						real_len;
				else
					l := 1;
				end if;
				if real_len + l > 80 then
					real_len := out_start - startp;
					exit;
				end if;
				if out_start = text_length then
					real_len := text_length - startp + 1;
					exit;
				end if;
				real_len := real_len + l;
				out_start := out_start + 1;
			end loop;
			-- Break line at last space/tab, semi-colon, linefeed,
			-- comma, tab, 80 chars, or end of string
			break_pos := instr(substr(x_str, startp, real_len),
				' '||chr(9), -1);
			lf_pos := instr(substr(x_str, startp, real_len),
				chr(10), -1);
			semi_pos := instr(substr(x_str, startp, real_len),
				';', -1);
			-- No break at space/tab if no characters come before it
			if break_pos > 0 then
				bef_chars := ltrim(substr(x_str, startp,
					break_pos - startp + 1));
				if bef_chars is null then
					break_pos := 0;
				end if;
			end if;
			-- See which break is encountered first
			last_break := 0; -- Init position of last break
			if lf_pos > last_break then
				-- Store break right before the linefeed
				last_break := lf_pos - 1;
				offset := 1; -- Skip linefeed
			end if;
			-- Check for break at a comma
			lf_pos := instr(substr(x_str, startp, real_len),
				',', -1);
			if lf_pos > last_break then
				-- Store break right after the comma
				last_break := lf_pos;
				offset := 0;
			end if;
			if break_pos = 0 then
				-- Check for break at a tab if no space/tab
				-- found
				lf_pos := instr(substr(x_str, startp,
					real_len), chr(9), -1);
				if lf_pos > last_break then
					-- Store break right before the tab
					last_break := lf_pos - 1;
					offset := 0;
				end if;
			end if;
			if semi_pos > last_break then
				-- Store break right after the semicolon
				last_break := semi_pos;
				offset := 0;
			else
				-- If there is a break at a semi-colon, skip
				-- this break at the tab
				if break_pos > last_break then
					-- Store break right after the space
					last_break := break_pos;
					-- Skip space part of the space/tab
					offset := 1;
				end if;
			end if;
			-- No break if rest of line is <= 80 characters
			if real_len = text_length - startp + 1 then
				-- Set to break at end of line
				last_break := real_len;
				offset := 0;
			end if;
			-- Break at the last break that was encountered
			break_pos := last_break;
			-- See if no break position found
			if break_pos = 0 then
				-- Back up to first space
				backwards := 1;
				-- Start looking for break at line end
				break_pos := real_len;
				while backwards = 1
				loop
					-- See if no space found
					if break_pos <= 1 then
						-- Break at 80 chars
						break_pos := real_len;
						backwards := 0;
						exit;
					end if;
					-- See if we found a space
					if substr(x_str, startp +
						break_pos - 1, 1) = ' '
					then
						backwards := 0;
						exit;
					end if;
					break_pos := break_pos - 1;
				end loop;
			end if;
			xchar := break_pos;
			-- Return output line
			if xchar = 0 then
				if offset = 0 then
					return my_lin;
				end if;
			else
				my_lin := wri(my_lin,
					substr(x_str, startp, xchar), 1);
			end if;
			startp := startp + xchar + offset;
		end loop;
		return my_lin;
	end brkline;
	procedure do_deferrable is
	begin
		if ora8 then
			if lv_deferrable = 'DEFERRABLE' then
				a_lin := wri(a_lin, ' DEFERRABLE', 0);
			elsif lv_deferred = 'DEFERRED' then
				a_lin := wri(a_lin, ' NOT DEFERRABLE', 0);
			end if;
			if lv_deferred = 'DEFERRED' then
				a_lin := wri(a_lin, ' INITIALLY DEFERRED', 0);
			end if;
		end if;
	end do_deferrable;
begin
	lineno := 0;
	a_lin := '';
	--
	-- See if Oracle8
	--
	open ora8_cursor;
	fetch ora8_cursor into lv_table_name;
	if ora8_cursor%found then
		ora8 := TRUE;
	else
		ora8 := FALSE;
	end if;
	close ora8_cursor;
	--
	-- Get database block size
	--
	open param_cursor;
	fetch param_cursor into db_block_size;
	if param_cursor%notfound then
		raise_application_error(-20000,
			'Can not find db_block_size v$parameter - Aborting...');
	end if;
	close param_cursor;
	--
	-- Fetch each constraint
	--
	open cons_cursor;
	loop
		fetch cons_cursor into lv_owner, lv_table_name,
			lv_constraint_name, lv_constraint_type,
			lv_search_condition, lv_r_table_name, lv_r_owner,
			lv_r_constraint_name, lv_delete_rule, lv_status;
		exit when cons_cursor%notfound;
		lv_deferrable := 'NOT DEFERRABLE';
		lv_deferred := 'IMMEDIATE';
		lv_validated := 'VALIDATED';
		if ora8 then
			cursor1 := dbms_sql.open_cursor;
			stmnt := 'select deferrable,deferred,validated' ||
				' from sys.dba_constraints' ||
				' where owner = ' || &q || lv_owner || &q ||
				' and constraint_name = ' || &q ||
				lv_constraint_name || &q;
			dbms_sql.parse(cursor1, stmnt, dbms_sql.native);
			dbms_sql.define_column(cursor1, 1, lv_deferrable, 14);
			dbms_sql.define_column(cursor1, 2, lv_deferred, 9);
			dbms_sql.define_column(cursor1, 3, lv_validated, 13);
			rows_processed := dbms_sql.execute(cursor1);
			if dbms_sql.fetch_rows(cursor1) <> 0 then
				dbms_sql.column_value(cursor1, 1,
					lv_deferrable);
				dbms_sql.column_value(cursor1, 2, lv_deferred);
				dbms_sql.column_value(cursor1, 3, lv_validated);
			end if;
			dbms_sql.close_cursor(cursor1);
		end if;
		columns_reqd := false;
		pending := false;
		if lv_constraint_type = 'C' then
			nn_pos := instr(lv_search_condition, ' IS NOT NULL');
			if nn_pos = 0 then
				a_lin := wri(a_lin, 'alter table ', 0);
				a_lin := wri(a_lin, lv_owner || '.' ||
					lv_table_name, 0);
				a_lin := wri(a_lin, ' add (', 0);
				if substr(lv_constraint_name, 1, 5) != 'SYS_C'
				then
					a_lin := wri(a_lin, 'constraint ', 0);
					a_lin := wri(a_lin, lv_constraint_name,
						0);
					a_lin := wri(a_lin, ' ', 0);
				end if;
				a_lin := wri(a_lin, 'check(', 0);
				a_lin := brkline(a_lin, lv_search_condition);
				a_lin := wri(a_lin, '))', 0);
				do_deferrable;
				pending := true;
			elsif '&tblsize_schema' = '*' then
				a_lin := wri(a_lin, 'alter table ', 0);
				a_lin := wri(a_lin, lv_owner || '.' ||
					lv_table_name, 0);
				a_lin := wri(a_lin, ' modify (', 0);
				a_lin := wri(a_lin, substr(lv_search_condition,
					1, nn_pos - 1), 0);
				if substr(lv_constraint_name, 1, 5) != 'SYS_C'
				then
					a_lin := wri(a_lin, ' constraint ', 0);
					a_lin := wri(a_lin, lv_constraint_name,
						0);
				end if;
				a_lin := wri(a_lin, ' NOT NULL)', 0);
				do_deferrable;
				pending := true;
			end if;
		end if;
		if lv_constraint_type = 'P' then
			a_lin := wri(a_lin, 'alter table ', 0);
			a_lin := wri(a_lin, lv_owner || '.' || lv_table_name,
				0);
			a_lin := wri(a_lin, ' add constraint ', 0);
			a_lin := wri(a_lin, lv_constraint_name, 0);
			a_lin := wri(a_lin, ' primary key (', 0);
			columns_reqd := true;
			pending := true;
		end if;
		if lv_constraint_type = 'R' then
			a_lin := wri(a_lin, 'alter table ', 0);
			a_lin := wri(a_lin, lv_owner || '.' || lv_table_name,
				0);
			a_lin := wri(a_lin, ' add constraint ', 0);
			a_lin := wri(a_lin, lv_constraint_name, 0);
			a_lin := wri(a_lin, ' foreign key (', 0);
			columns_reqd := true;
			pending := true;
		end if;
		if lv_constraint_type = 'U' then
			a_lin := wri(a_lin, 'alter table ', 0);
			a_lin := wri(a_lin, lv_owner || '.' || lv_table_name,
				0);
			a_lin := wri(a_lin, ' add constraint ', 0);
			a_lin := wri(a_lin, lv_constraint_name, 0);
			a_lin := wri(a_lin, ' unique (', 0);
			columns_reqd := true;
			pending := true;
		end if;
		if columns_reqd then
			open cons_cols_cursor(lv_owner, lv_constraint_name,
				lv_table_name);
			loop
				fetch cons_cols_cursor into
					lv_column_name, lv_position;
				exit when cons_cols_cursor%notfound;
				if lv_position <> 1 then
					a_lin := wri(a_lin, ', ', 0);
				end if;
				if length(lv_column_name) < 78 then
					a_lin := wri(a_lin, chr(34) ||
						lv_column_name || chr(34), 0);
				else
					a_lin := brkline(a_lin, lv_column_name);
				end if;
			end loop;
			close cons_cols_cursor;
			a_lin := wri(a_lin, ')', 0);
		end if;
		if lv_constraint_type = 'P' or lv_constraint_type = 'U' then
			do_deferrable;
			lv_logging := 'YES';
			lv_buffer_pool := 'DEFAULT';
			lv_partitioned := 'NO';
			found_index := false;
			cursor1 := dbms_sql.open_cursor;
			if ora8 then
				stmnt := 'select tablespace_name,ini_trans,' ||
					'max_trans,initial_extent,' ||
					'next_extent,min_extents,' ||
					'max_extents,pct_increase,pct_free,' ||
					'freelists,freelist_groups,' ||
					'logging,partitioned,buffer_pool' ||
					' from sys.dba_indexes' ||
					' where owner = ' || &q || lv_owner ||
					&q || ' and index_name = ' || &q ||
					lv_constraint_name || &q;
			else
				stmnt := 'select tablespace_name,ini_trans,' ||
					'max_trans,initial_extent,' ||
					'next_extent,min_extents,' ||
					'max_extents,pct_increase,pct_free,' ||
					'freelists,freelist_groups' ||
					' from sys.dba_indexes' ||
					' where owner = ' || &q || lv_owner ||
					&q || ' and index_name = ' || &q ||
					lv_constraint_name || &q;
			end if;
			dbms_sql.parse(cursor1, stmnt, dbms_sql.native);
			dbms_sql.define_column(cursor1, 1, lv_tablespace_name,
				30);
			dbms_sql.define_column(cursor1, 2, lv_ini_trans);
			dbms_sql.define_column(cursor1, 3, lv_max_trans);
			dbms_sql.define_column(cursor1, 4, lv_initial_extent);
			dbms_sql.define_column(cursor1, 5, lv_next_extent);
			dbms_sql.define_column(cursor1, 6, lv_min_extents);
			dbms_sql.define_column(cursor1, 7, lv_max_extents);
			dbms_sql.define_column(cursor1, 8, lv_pct_increase);
			dbms_sql.define_column(cursor1, 9, lv_pct_free);
			dbms_sql.define_column(cursor1, 10, lv_freelists);
			dbms_sql.define_column(cursor1, 11, lv_freelist_groups);
			if ora8 then
				dbms_sql.define_column(cursor1, 12, lv_logging,
					3);
				dbms_sql.define_column(cursor1, 13,
					lv_partitioned, 3);
				dbms_sql.define_column(cursor1, 14,
					lv_buffer_pool, 7);
			end if;
			rows_processed := dbms_sql.execute(cursor1);
			if dbms_sql.fetch_rows(cursor1) <> 0 then
				dbms_sql.column_value(cursor1, 1,
					lv_tablespace_name);
				dbms_sql.column_value(cursor1, 2, lv_ini_trans);
				dbms_sql.column_value(cursor1, 3, lv_max_trans);
				dbms_sql.column_value(cursor1, 4,
					lv_initial_extent);
				dbms_sql.column_value(cursor1, 5,
					lv_next_extent);
				dbms_sql.column_value(cursor1, 6,
					lv_min_extents);
				dbms_sql.column_value(cursor1, 7,
					lv_max_extents);
				dbms_sql.column_value(cursor1, 8,
					lv_pct_increase);
				dbms_sql.column_value(cursor1, 9, lv_pct_free);
				dbms_sql.column_value(cursor1, 10,
					lv_freelists);
				dbms_sql.column_value(cursor1, 11,
					lv_freelist_groups);
				if ora8 then
					dbms_sql.column_value(cursor1, 12,
						lv_logging);
					dbms_sql.column_value(cursor1, 13,
						lv_partitioned);
					dbms_sql.column_value(cursor1, 14,
						lv_buffer_pool);
				end if;
				found_index := true;
				a_lin := wri(a_lin, ' using index', 0);
				--
				-- Workaround for Oracle bug if partitioned
				-- index is found:  We will use the existing
				-- unique index, by just specifying only the
				-- 'using index' clause.
				--
				if lv_partitioned = 'NO' then
					a_lin := wri(a_lin, ' tablespace ', 0);
					a_lin := wri(a_lin, lv_tablespace_name,
						0);
					if lv_logging = 'NO' then
						a_lin := wri(a_lin,
							' nologging', 0);
					end if;
					/* Calculate extent sizes in Mbytes */
					/* or Kbytes, if possible */
					if mod(lv_initial_extent, 1048576) = 0
					then
						initial_extent_size := to_char(
							lv_initial_extent /
							1048576) || 'M';
					elsif mod(lv_initial_extent, 1024) = 0
					then
						initial_extent_size := to_char(
							lv_initial_extent /
							1024) || 'K';
					else
						initial_extent_size := to_char(
							lv_initial_extent);
					end if;
					if mod(lv_next_extent, 1048576) = 0 then
						next_extent_size :=
							to_char(lv_next_extent /
							1048576) || 'M';
					elsif mod(lv_next_extent, 1024) = 0 then
						next_extent_size :=
							to_char(lv_next_extent /
							1024) || 'K';
					else
						next_extent_size :=
							to_char(lv_next_extent);
					end if;
					a_lin := wri(a_lin, ' storage(', 0);
					a_lin := wri(a_lin, 'initial ', 0);
					a_lin := wri(a_lin, initial_extent_size,
						0);
					a_lin := wri(a_lin, ' next ', 0);
					a_lin := wri(a_lin, next_extent_size,
						0);
					a_lin := wri(a_lin, ' pctincrease ', 0);
					a_lin := wri(a_lin, lv_pct_increase, 0);
					a_lin := wri(a_lin, ' minextents ', 0);
					a_lin := wri(a_lin, lv_min_extents, 0);
					a_lin := wri(a_lin, ' maxextents ', 0);
					if lv_max_extents > 999999 then
						a_lin := wri(a_lin,
							'unlimited', 0);
					else
						a_lin := wri(a_lin,
							lv_max_extents, 0);
					end if;
					if ora8 then
						if lv_buffer_pool is not null
						then
							if lv_buffer_pool =
								'KEEP'
							then
								a_lin :=
								  wri(a_lin,
								  ' buffer' ||
								  '_pool keep',
								  0);
							elsif lv_buffer_pool =
								'RECYCLE'
							then
								a_lin :=
								  wri(a_lin,
								  ' buffer' ||
								  '_pool' ||
								  ' recycle',
								  0);
							end if;
						end if;
					end if;
					a_lin := wri(a_lin, ') ', 0);
					a_lin := wri(a_lin, 'pctfree ', 0);
					a_lin := wri(a_lin, lv_pct_free, 0);
					a_lin := wri(a_lin, ' initrans ', 0);
					a_lin := wri(a_lin, lv_ini_trans, 0);
					a_lin := wri(a_lin, ' maxtrans ', 0);
					a_lin := wri(a_lin, lv_max_trans, 0);
					if lv_freelists = 0 then
						lv_freelists := 1;
					end if;
					if lv_freelist_groups = 0 then
						lv_freelist_groups := 1;
					end if;
					if lv_freelists is not null then
						if lv_freelists <> 1 then
							a_lin := wri(a_lin,
								' freelists ' ||
								to_char(
								lv_freelists),
								0);
						end if;
					end if;
					if lv_freelist_groups is not null then
						if lv_freelist_groups <> 1 then
							a_lin := wri(a_lin,
								' freelist' ||
								' groups ' ||
								to_char(
							      lv_freelist_groups
								), 0);
						end if;
					end if;
				end if;
			end if;
			dbms_sql.close_cursor(cursor1);
			if not found_index then
				--
				-- Indicate that we want to use an existing
				-- unique index to support this constraint
				--
				a_lin := wri(a_lin, ' using index', 0);
			end if;
		end if;
		if lv_constraint_type = 'R' then
			open cons_ref_cursor (lv_r_owner, lv_r_constraint_name);
			fetch cons_ref_cursor into lv_cons_ref_table_name;
			if cons_ref_cursor%notfound then
				raise no_ref_cons;
			end if;
			close cons_ref_cursor;
			a_lin := wri(a_lin, ' references ', 0);
			a_lin := wri(a_lin, lv_r_owner || '.' ||
				lv_cons_ref_table_name || '(', 0);
			open cons_cols_cursor (lv_r_owner, lv_r_constraint_name,
				lv_r_table_name);
			loop
				fetch cons_cols_cursor into
					lv_column_name, lv_position;
				exit when cons_cols_cursor%notfound;
				if lv_position <> 1 then
					a_lin := wri(a_lin, ', ', 0);
				end if;
				if length(lv_column_name) < 78 then
					a_lin := wri(a_lin, chr(34) ||
						lv_column_name || chr(34), 0);
				else
					a_lin := brkline(a_lin, lv_column_name);
				end if;
			end loop;
			close cons_cols_cursor;
			a_lin := wri(a_lin, ')', 0);
			if lv_delete_rule = 'CASCADE' then
				a_lin := wri(a_lin,
					' on delete cascade', 0);
			end if;
			do_deferrable;
		end if;
		if pending then
			if lv_status = 'DISABLED' then
				a_lin := wri(a_lin, ' DISABLE', 0);
			elsif lv_validated = 'NOT VALIDATED' then
				a_lin := wri(a_lin, ' ENABLE NOVALIDATE', 0);
			end if;
			a_lin := wri(a_lin, ';', 1);
		end if;
	end loop;
	close cons_cursor;
	commit;
exception
	when no_ref_cons then
		if dbms_sql.is_open(cursor2) then
			dbms_sql.close_cursor(cursor2);
		end if;
		if dbms_sql.is_open(cursor1) then
			dbms_sql.close_cursor(cursor1);
		end if;
		rollback;
		raise_application_error(-20000,
			'Error - Can not find on ref constraint for ' ||
			lv_r_owner || ',' || lv_r_constraint_name || chr(10) ||
			'Aborting...');
	when others then
		if dbms_sql.is_open(cursor2) then
			dbms_sql.close_cursor(cursor2);
		end if;
		if dbms_sql.is_open(cursor1) then
			dbms_sql.close_cursor(cursor1);
		end if;
		rollback;
		raise_application_error(-20000,
			'Unexpected error on ' || lv_constraint_name || ': ' ||
			sqlerrm || chr(10) || 'stmnt is ' || stmnt || chr(10) ||
			'Aborting...');
end;
/
set termout off
set trimspool on
spool cr_const.sql
select 'rem cr_const.sql' from dual;
select 'rem' from dual;
select 'rem ***** All constraints for database ' || name from v$database;
select 'rem' from dual;
select 'set define off' from dual;
select 'set echo on' from dual;
select 'set feedback off' from dual;
select 'set termout on' from dual;
select 'set verify off' from dual;
select 'spool cr_const.lst' from dual;
select rtrim(lin) from crconstr_det_tmp order by linen;
select 'spool off' from dual;
spool off
set termout on
prompt Created cr_const.sql
drop table crconstr_det_tmp;
exit

 

Back


user comments and suggestions are invited at KmailDrive@gmail.com