Re: Generating SQL to recreate constraints.

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 5 Oct 1999 10:00:47 +0200
Message-ID: <7tcb90$ckb$1@oceanite.cybercable.fr>

Here's a script that genrate a script recreating all the check constraints of the calling user.

Set echo OFF
Set feedback OFF
Set heading OFF
Set linesize 2000
Set long 32760
Set newpage 0
Set pagesize 0
Set recsep OFF
Set space 0
Set trimout ON
Set trimspool ON
BTitle OFF
TTitle OFF

Create table mcsql

   (id number, subid number,
    codesql varchar2(2000),
    primary key (id, subid))
/

spool script.sql

Declare

   Cursor cc is

      select table_name, constraint_name, search_condition
      from user_constraints
      where owner = USER
        and constraint_type = 'C'
      order by table_name, constraint_name;

   contrainte varchar2(32760);
   nomTable user_constraints.table_name%TYPE;    nomCons user_constraints.constraint_name%TYPE;    codeSql varchar2(32760);
   valeur varchar2(2000);
   id number := 0;
   subid number;
   i number;
   lg number;
   espace boolean;
Begin

   Open cc;
   Loop

      Fetch cc into nomTable, nomCons, contrainte;
      Exit when cc%NOTFOUND;
      codeSql := '
Alter table '||nomTable||' add ';
      If ( substr(nomCons, 1, 5) != 'SYS_C' ) then
         codeSql := codeSql||'constraint '||nomCons;
      End if;
      codeSql := codeSql||'
   check ('||contrainte||');';
      subid := 0;
      i := 1;
      lg := 0;
      espace := False;
      While ( i <= length (codeSql) ) loop
         If ( (i+2000) <= length (codeSql) ) then
            lg := 2000;
            While ( substr(codeSql, i+lg, 1) != '
' and lg > 0 ) loop
               lg := lg - 1;
            End loop;
            If ( lg = 0 ) then
               lg := 2000;
               While ( substr(codeSql, i+lg, 1) != ' ' and lg > 0 ) loop
                  lg := lg - 1;
               End loop;
               If ( lg = 0 ) then
                  lg := 2000;
               Else
                  lg := lg - 1;
                  espace := True;
               End if;
            Else
               lg := lg - 1;
               espace := True;
            End if;
         Else
            lg := length (codeSql) - i + 1;
         End if;
         If ( lg > 0 ) then
            valeur := substr(codeSql, i, lg);
            Insert into mcsql values (id, subid, valeur);
            subid := subid + 1;
            i := i + lg;
         End if;
         If ( espace ) then
            i := i + 1;
         End if;
      End loop;
      id := id + 1;

   End loop;
   Close cc;
   Commit;
End;
/
Select codeSql from mcsql order by id, subid /

spool off

Drop table mcsql
/

Set embedded OFF
Set feedback ON
Set heading ON
Set linesize 80
Set newpage 1
Set pagesize 500
Set recsep WRAPPED
Set space 1
Set termout ON

Then you have to execute the generated script: @script

--
Regards

Michel

NetComrade <andreyNSPAM_at_bookexchange.net> a écrit dans le message : 37f8f33a.524585683_at_news.earthlink.net...
> Hi,
>
> Anybody has a nice script to regenerate all constraints on a table?
> Dealing with CHECK constraint is a bitch, it is stored in LONG.
> (SEACH_CONDITION)
> Also dealing with catentated indexes/constraints is tough, b/c of
> position.
>
> Thanx.
> ---------------
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email
>
Received on Tue Oct 05 1999 - 03:00:47 CDT