-- Description : Creates the DDL to recreate the specified table.
-- Call Syntax : @recreate_table (table-name) (schema-name)
SET SERVEROUTPUT ON
SET LINESIZE 100
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL f:\ReCreate_&&1
PROMPT
-- ----------------------------------------------
-- Reset the buffer size and display script title
-- ----------------------------------------------
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('-- Description : Re-creation script for ' || Upper('&&1'));
DBMS_Output.Put_Line('-------------------------------------------------------------');
END;
/
-- ------------------------------------
-- Drop existing FKs to specified table
-- ------------------------------------
DECLARE
CURSOR cu_fks IS
SELECT *
FROM all_constraints a
WHERE a.owner = Upper('&&2')
AND a.constraint_type = 'R'
AND a.r_constraint_name IN (SELECT a1.constraint_name
FROM all_constraints a1
WHERE a1.table_name = Upper('&&1')
AND a1.owner = Upper('&&2'));
BEGIN
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Droping Foreign Keys to ' || Upper('&&1'));
FOR cur_rec IN cu_fks LOOP
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
END LOOP;
END;
/
-- -----------------
-- Drop FKs on table
-- -----------------
DECLARE
CURSOR cu_fks IS
SELECT *
FROM all_constraints a
WHERE a.constraint_type = 'R'
AND a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Droping Foreign Keys on ' || Upper('&&1'));
FOR cur_rec IN cu_fks LOOP
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
END LOOP;
END;
/
-- -------------------------
-- Drop constraints on table
-- -------------------------
DECLARE
CURSOR cu_cons IS
SELECT *
FROM all_constraints a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2')
AND a.constraint_type IN ('P','U');
-- ----------------------------------------------------------------------------------------
FUNCTION Con_Columns(p_tab IN VARCHAR2,
p_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu_col_cursor IS
SELECT a.column_name
FROM all_cons_columns a
WHERE a.table_name = p_tab
AND a.constraint_name = p_con
AND a.owner = Upper('&&2')
ORDER BY a.position;
l_result VARCHAR2(1000);
BEGIN
FOR cur_rec IN cu_col_cursor LOOP
IF cu_col_cursor%ROWCOUNT = 1 THEN
l_result := cur_rec.column_name;
ELSE
l_result := l_result || ',' || cur_rec.column_name;
END IF;
END LOOP;
RETURN Lower(l_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Droping Constraints on ' || Upper('&&1'));
FOR cur_rec IN cu_cons LOOP
IF cur_rec.constraint_type = 'P' THEN
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP PRIMARY KEY;');
ELSIF cur_rec.constraint_type = 'R' THEN
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
ELSIF cur_rec.constraint_type = 'U' THEN
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP UNIQUE (' || Con_Columns(cur_rec.table_name, cur_rec.constraint_name) || ');');
END IF;
END LOOP;
END;
/
-- ---------------------
-- Drop indexes on table
-- ---------------------
DECLARE
CURSOR cu_idx IS
SELECT *
FROM all_indexes a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Droping Indexes on ' || Upper('&&1'));
FOR cur_rec IN cu_idx LOOP
DBMS_Output.Put_Line('DROP INDEX ' || Lower(cur_rec.index_name) || ';');
END LOOP;
END;
/
-- -----------------------------------------
-- Rename existing table - prefix with 'tmp'
-- -----------------------------------------
SET VERIFY OFF
SET FEEDBACK OFF
BEGIN
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Renaming ' || Upper('&&1') || ' to TMP_' || Upper('&&1'));
DBMS_Output.Put_Line('RENAME ' || Lower('&&1') || ' TO tmp_' || Lower('&&1'));
DBMS_Output.Put_Line('/');
END;
/
-- ---------------
-- Re-Create table
-- ---------------
DECLARE
CURSOR cu_tab IS
SELECT a.table_name,
a.pct_free,
a.pct_used,
a.ini_trans,
a.max_trans,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.min_extents,
a.max_extents,
a.pct_increase
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.owner = Upper('&&2');
CURSOR cu_col (in_table IN VARCHAR2) IS
SELECT a.column_name,
a.data_type,
Decode(a.data_scale,NULL,'',0,'',',' || To_Char(a.data_scale)) data_scale,
To_Char(a.data_length) data_length,
To_Char(a.data_precision) data_precision,
Decode(a.nullable,'Y','NULL','N','NOT NULL') nullable
FROM all_tab_columns a
WHERE a.table_name = in_table
AND a.owner = Upper('&&2');
CURSOR cu_tab_com IS
SELECT a.table_name,
a.comments
FROM all_tab_comments a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.owner = Upper('&&2')
AND a.comments IS NOT NULL;
CURSOR cu_col_com IS
SELECT a.table_name,
a.column_name,
a.comments
FROM all_col_comments a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.owner = Upper('&&2')
AND a.comments IS NOT NULL;
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Script does not cope with DEFAULT VALUES or Check Constraints');
FOR cur_tab IN cu_tab LOOP
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Creating Table ' || cur_tab.table_name);
DBMS_Output.Put_Line('CREATE TABLE ' || Lower(cur_tab.table_name));
DBMS_Output.Put_Line('(');
FOR cur_col IN cu_col (in_table => cur_tab.table_name) LOOP
IF cu_col%ROWCOUNT != 1 THEN
DBMS_Output.Put_Line(',');
END IF;
IF cur_col.data_type = 'DATE' THEN
DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable);
ELSIF cur_col.data_type = 'NUMBER' THEN
DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_precision || cur_col.data_scale || ')',20,' ') || ' ' || cur_col.nullable);
ELSE
DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_length || ')',20,' ') || ' ' || cur_col.nullable);
END IF;
END LOOP;
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line(')');
DBMS_Output.Put_Line('PCTFREE ' || cur_tab.pct_free);
DBMS_Output.Put_Line('PCTUSED ' || cur_tab.pct_used);
DBMS_Output.Put_Line('INITRANS ' || cur_tab.ini_trans);
DBMS_Output.Put_Line('MAXTRANS ' || cur_tab.max_trans);
DBMS_Output.Put_Line('TABLESPACE ' || cur_tab.tablespace_name);
DBMS_Output.Put_Line('STORAGE (');
DBMS_Output.Put_Line(' INITIAL ' || Trunc(cur_tab.initial_extent/1024) || 'K');
DBMS_Output.Put_Line(' NEXT ' || Trunc(cur_tab.next_extent/1024) || 'K');
DBMS_Output.Put_Line(' MINEXTENTS ' || cur_tab.min_extents);
DBMS_Output.Put_Line(' MAXEXTENTS ' || cur_tab.max_extents);
DBMS_Output.Put_Line(' PCTINCREASE ' || cur_tab.pct_increase);
DBMS_Output.Put_Line(' )');
DBMS_Output.Put_Line('/');
DBMS_Output.Put_Line(' ');
END LOOP;
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Creating Table Comment');
FOR cur_tab_com IN cu_tab_com LOOP
DBMS_Output.Put_Line('COMMENT ON TABLE ' || Lower(cur_tab_com.table_name) || ' IS ''' || cur_tab_com.comments || ''';');
END LOOP;
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Creating Column Comments');
FOR cur_col_com IN cu_col_com LOOP
DBMS_Output.Put_Line('COMMENT ON COLUMN ' || Lower(cur_col_com.table_name || '.' || cur_col_com.column_name) || ' IS ''' || cur_col_com.comments || ''';');
END LOOP;
DBMS_Output.Put_Line(' ');
END;
/
-- ---------------------
-- Re-Create constraints
-- ---------------------
DECLARE
CURSOR cu_con IS
SELECT *
FROM all_constraints a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.owner = Upper('&&2')
AND a.constraint_type IN ('U','P')
ORDER BY a.constraint_name;
CURSOR cu_idx (p_idx IN VARCHAR2) IS
SELECT *
FROM all_indexes a
WHERE a.index_name = p_idx
AND a.table_owner = Upper('&&2')
ORDER BY a.index_name;
-- ----------------------------------------------------------------------------------------
FUNCTION Con_Columns(p_tab IN VARCHAR2,
p_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu_col_cursor IS
SELECT a.column_name
FROM all_cons_columns a
WHERE a.table_name = p_tab
AND a.constraint_name = p_con
AND a.owner = Upper('&&2')
ORDER BY a.position;
l_result VARCHAR2(1000);
BEGIN
FOR cur_rec IN cu_col_cursor LOOP
IF cu_col_cursor%ROWCOUNT = 1 THEN
l_result := cur_rec.column_name;
ELSE
l_result := l_result || ',' || cur_rec.column_name;
END IF;
END LOOP;
RETURN Lower(l_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
FOR cur_rec IN cu_con LOOP
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Creating Constraint ' || cur_rec.constraint_name);
DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' ADD');
DBMS_Output.Put_Line('(');
DBMS_Output.Put_Line(' CONSTRAINT ' || Lower(cur_rec.constraint_name));
IF cur_rec.constraint_type = 'U' THEN
DBMS_Output.Put_Line(' UNIQUE ' || ' ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
ELSE
DBMS_Output.Put_Line(' PRIMARY KEY ' || ' ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
END IF;
FOR cur_idx IN cu_idx (cur_rec.constraint_name) LOOP
DBMS_Output.Put_Line(' USING INDEX');
DBMS_Output.Put_Line(' INITRANS ' || cur_idx.ini_trans);
DBMS_Output.Put_Line(' MAXTRANS ' || cur_idx.max_trans);
DBMS_Output.Put_Line(' PCTFREE ' || cur_idx.pct_free);
DBMS_Output.Put_Line(' TABLESPACE ' || cur_idx.tablespace_name);
DBMS_Output.Put_Line(' STORAGE (');
DBMS_Output.Put_Line(' INITIAL ' || Trunc(cur_idx.initial_extent/1024) || 'K');
DBMS_Output.Put_Line(' NEXT ' || Trunc(cur_idx.next_extent/1024) || 'K');
DBMS_Output.Put_Line(' MINEXTENTS ' || cur_idx.min_extents);
DBMS_Output.Put_Line(' MAXEXTENTS ' || cur_idx.max_extents);
DBMS_Output.Put_Line(' PCTINCREASE ' || cur_idx.pct_increase);
DBMS_Output.Put_Line(' )');
END LOOP;
DBMS_Output.Put_Line(')');
DBMS_Output.Put_Line('/');
DBMS_Output.Put_Line(' ');
END LOOP;
END;
/
-- ---------------------
-- Recreate FKs on table
-- ---------------------
DECLARE
CURSOR cu_cons IS
SELECT a.table_name,
a.constraint_name,
a.r_constraint_name,
NVL(a.delete_rule,' ') delete_rule,
NVL(a.status,' ') status
FROM all_constraints a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.owner = Upper('&&2')
AND a.constraint_type = 'R'
ORDER BY a.table_name, a.constraint_name;
v_r_table_name all_constraints.table_name%TYPE;
-- ----------------------------------------------------------------------------------------
FUNCTION Con_Columns(p_tab IN VARCHAR2,
p_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu_col_cursor IS
SELECT a.column_name
FROM all_cons_columns a
WHERE a.table_name = p_tab
AND a.constraint_name = p_con
AND a.owner = Upper('&&2')
ORDER BY a.position;
l_result VARCHAR2(1000);
BEGIN
FOR cur_rec IN cu_col_cursor LOOP
IF cu_col_cursor%ROWCOUNT = 1 THEN
l_result := cur_rec.column_name;
ELSE
l_result := l_result || ',' || cur_rec.column_name;
END IF;
END LOOP;
RETURN Lower(l_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
FOR cur_rec IN cu_cons LOOP
SELECT a.table_name
INTO v_r_table_name
FROM all_constraints a
WHERE a.constraint_name = cur_rec.r_constraint_name;
DBMS_Output.Put_Line('ALTER TABLE '||Lower(cur_rec.table_name)||' ADD');
DBMS_Output.Put_Line(' (');
DBMS_Output.Put_Line(' CONSTRAINT '||Lower(cur_rec.constraint_name));
DBMS_Output.Put_Line(' FOREIGN KEY ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
DBMS_Output.Put_Line(' REFERENCES '||Lower(v_r_table_name)||' ('||Con_Columns(v_r_table_name, cur_rec.r_constraint_name)||')');
IF cur_rec.delete_rule = 'CASCADE' THEN
DBMS_Output.Put_Line(' ON DELETE CASCADE');
END IF;
IF cur_rec.status = 'DISABLE' THEN
DBMS_Output.Put_Line(' DISABLE');
END IF;
DBMS_Output.Put_Line(' )');
DBMS_Output.Put_Line('/');
DBMS_Output.Put_Line(' ');
END LOOP;
END;
/
-- -----------------
-- Re-Create indexes
-- -----------------
DECLARE
CURSOR cu_idx IS
SELECT Decode(a.uniqueness,'UNIQUE','UNIQUE ',NULL) AS uniqueness,
a.index_name,
a.table_name,
a.ini_trans,
a.max_trans,
a.pct_free,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.min_extents,
a.max_extents,
a.pct_increase
FROM all_indexes a
WHERE a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
AND a.table_owner = Upper('&&2')
AND NOT EXISTS (SELECT '1'
FROM all_constraints b
WHERE b.constraint_name = a.index_name
AND b.constraint_type = 'P'
AND b.owner = Upper('&&2'))
ORDER BY a.index_name;
CURSOR cu_col (p_idx IN VARCHAR2) IS
SELECT a.column_name
FROM all_ind_columns a
WHERE a.index_name = p_idx
AND a.index_owner = Upper('&&2')
ORDER BY a.column_position;
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
FOR cur_idx IN cu_idx LOOP
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Creating Index ' || cur_idx.index_name);
DBMS_Output.Put_Line('CREATE ' || cur_idx.uniqueness || 'INDEX ' || Lower(cur_idx.index_name));
DBMS_Output.Put_Line('ON ' || Lower(cur_idx.table_name));
DBMS_Output.Put_Line('(');
FOR cur_col IN cu_col (cur_idx.index_name) LOOP
IF cu_col%ROWCOUNT != 1 THEN
DBMS_Output.Put_Line(',');
END IF;
DBMS_Output.Put(' ' || lower(cur_col.column_name));
END LOOP;
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line(')');
DBMS_Output.Put_Line('INITRANS ' || cur_idx.ini_trans);
DBMS_Output.Put_Line('MAXTRANS ' || cur_idx.max_trans);
DBMS_Output.Put_Line('PCTFREE ' || cur_idx.pct_free);
DBMS_Output.Put_Line('TABLESPACE ' || cur_idx.tablespace_name);
DBMS_Output.Put_Line('STORAGE (');
DBMS_Output.Put_Line(' INITIAL ' || Trunc(cur_idx.initial_extent/1024) || 'K');
DBMS_Output.Put_Line(' NEXT ' || Trunc(cur_idx.next_extent/1024) || 'K');
DBMS_Output.Put_Line(' MINEXTENTS ' || cur_idx.min_extents);
DBMS_Output.Put_Line(' MAXEXTENTS ' || cur_idx.max_extents);
DBMS_Output.Put_Line(' PCTINCREASE ' || cur_idx.pct_increase);
DBMS_Output.Put_Line(' )');
DBMS_Output.Put_Line('/');
DBMS_Output.Put_Line(' ');
END LOOP;
END;
/
-- --------------------------
-- Build up population insert
-- --------------------------
SET VERIFY OFF
SET FEEDBACK OFF
DECLARE
CURSOR cu_columns IS
SELECT Lower(column_name) column_name
FROM all_tab_columns atc
WHERE atc.table_name = Upper('&&1')
AND atc.owner = Upper('&&2');
BEGIN
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Populating ' || Upper('&&1') || ' from TPM_' || Upper('&&1'));
DBMS_Output.Put_Line('INSERT INTO ' || Lower('&&1'));
DBMS_Output.Put('SELECT ');
FOR cur_rec IN cu_columns LOOP
IF cu_columns%ROWCOUNT != 1 THEN
DBMS_Output.Put_Line(',');
END IF;
DBMS_Output.Put(' a.' || cur_rec.column_name);
END LOOP;
DBMS_Output.New_Line;
DBMS_Output.Put_Line('FROM tmp_' || Lower('&&1') || ' a');
DBMS_Output.Put_Line('/');
-- --------------
-- Drop tmp table
-- --------------
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Droping TMP_' || Upper('&&1'));
DBMS_Output.Put_Line('DROP TABLE tmp_' || Lower('&&1'));
DBMS_Output.Put_Line('/');
END;
/
-- ---------------------
-- Recreate FKs to table
-- ---------------------
DECLARE
CURSOR cu_cons IS
SELECT a.table_name,
a.constraint_name,
a.r_constraint_name,
NVL(a.delete_rule,' ') delete_rule,
NVL(a.status,' ') status
FROM all_constraints a
WHERE a.r_constraint_name IN (SELECT b.constraint_name
FROM all_constraints b
WHERE b.table_name = Upper('&&1')
AND b.owner = Upper('&&2')
AND b.constraint_type IN ('P', 'U') )
AND a.owner = Upper('&&2')
ORDER BY a.table_name, a.constraint_name;
-- ----------------------------------------------------------------------------------------
FUNCTION Con_Columns(p_tab IN VARCHAR2,
p_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu_col_cursor IS
SELECT a.column_name
FROM all_cons_columns a
WHERE a.table_name = p_tab
AND a.constraint_name = p_con
AND a.owner = Upper('&&2')
ORDER BY a.position;
l_result VARCHAR2(1000);
BEGIN
FOR cur_rec IN cu_col_cursor LOOP
IF cu_col_cursor%ROWCOUNT = 1 THEN
l_result := cur_rec.column_name;
ELSE
l_result := l_result || ',' || cur_rec.column_name;
END IF;
END LOOP;
RETURN Lower(l_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
DBMS_Output.Disable;
DBMS_Output.Enable(1000000);
DBMS_Output.Put_Line('PROMPT');
DBMS_Output.Put_Line('PROMPT Createing Foreign Keys to ' || Upper('&&1'));
FOR cur_rec IN cu_cons LOOP
DBMS_Output.Put_Line('ALTER TABLE '||Lower(cur_rec.table_name)||' ADD');
DBMS_Output.Put_Line('(');
DBMS_Output.Put_Line(' CONSTRAINT '||Lower(cur_rec.constraint_name));
DBMS_Output.Put_Line(' FOREIGN KEY ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
DBMS_Output.Put_Line(' REFERENCES '||Lower('&&1')||' ('||Con_Columns(Upper('&&1'), cur_rec.r_constraint_name)||')');
IF cur_rec.delete_rule = 'CASCADE' THEN
DBMS_Output.Put_Line(' ON DELETE CASCADE');
END IF;
IF cur_rec.status = 'DISABLE' THEN
DBMS_Output.Put_Line(' DISABLE');
END IF;
DBMS_Output.Put_Line(')');
DBMS_Output.Put_Line('/');
DBMS_Output.Put_Line(' ');
END LOOP;
END;
/
SET VERIFY OFF
SET FEEDBACK OFF
BEGIN
DBMS_Output.Put_Line(' ');
DBMS_Output.Put_Line('-------------------------------------------------------------');
DBMS_Output.Put_Line('-- END Re-creation script for ' || Upper('&&1'));
DBMS_Output.Put_Line('-------------------------------------------------------------');
END;
/
SPOOL OFF
PROMPT
SET VERIFY ON
SET FEEDBACK ON
SET TERMOUT ON