constraint.sql

rem 
------------------------------------------------------------------------------ 
rem Module Name : desct.sql 
rem               @desct table_name 
rem Purpose     : Lists all constraints for the user specified table. 
rem               Primary & unique Keys are shown with composite structure 
rem               Columns with Referances display object owner,object and 
column. 
rem Notes       : Tested for Oracle 7.3. 
rem               Display of Report is of 132 Characters format. 
rem 
------------------------------------------------------------------------------ 
 
set define on 
set feedback off 
set echo off 
set verify off 
set head on 
 
rem ********* TABLE CREATION ********************************** 
 
DECLARE 
  cur integer; 
  rc  integer; 
 
BEGIN 
       SELECT 1 
         INTO rc 
         FROM dba_tables 
        WHERE table_name = 'STAB' 
          AND owner = ( SELECT user from dual ); 
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
                cur := DBMS_SQL.OPEN_CURSOR; 
                DBMS_SQL.PARSE(cur, 'CREATE TABLE STAB ( SEQ number, 
                                  OUSER varchar2(30),  
                                  OWNER       varchar2(30), 
                                  TNAME       varchar2(30), 
                                  CNAME       varchar2(30), 
                                  NULLTYPE    varchar2(10), 
                                  TYPE        varchar2(15), 
                                  PK          varchar2(3), 
                                  FK          varchar2(3), 
                                  CK          varchar2(3), 
                                  UQ          varchar2(3), 
                                  DELETE_RULE varchar2(9), 
                                  CONDITION   varchar2(2000) )', 
DBMS_SQL.NATIVE); 
                          rc := DBMS_SQL.EXECUTE(cur); 
                          DBMS_SQL.CLOSE_CURSOR(cur); 
END; 
 
/ 
 
 
rem ********* INSERT COLUMN DETAILS ********************************** 
 
DELETE FROM stab 
  WHERE ouser = ( SELECT user||userenv('terminal') from dual); 
 
 
INSERT INTO stab( SEQ,OUSER,OWNER,TNAME, CNAME, NULLtype, Type, ck )  
SELECT rownum, user||userenv('terminal'), owner,table_name, column_name, 
       decode(nullable,'N','NOT NULL', null ) NULLABLE , 
       data_type || decode(data_type, 'DATE', NULL, 'LONG', NULL, 'LONG RAW', 
NULL, 
                         'NUMBER', decode(data_precision,NULL,NULL,'('), '(')  
                 || decode(data_type, 'DATE', NULL, 'LONG', NULL, 'LONG RAW', 
NULL, 
                                   'NUMBER', decode(data_precision, NULL, 
NULL, to_char(data_precision) ||  
                                                   (decode(data_scale, 0, 
NULL, ','||to_char(data_scale)))), 
                                   to_char(data_length) )  
                 || decode(data_type, 'DATE', NULL, 'LONG', NULL, 'LONG RAW', 
NULL, 
                         'NUMBER', decode(data_precision,NULL,NULL,')'), ')')  
Type,  
       decode(nullable,'N','C', null )  
       from dba_tab_columns 
       where table_name = upper('&1') 
/ 
 
COMMIT; 
 
 
rem ********* CONSTRAINTS UPDATES ********************************** 
 
DECLARE 
    -- Declaration Block 
---------------------------------------------------------------------------- 
        CURSOR consts IS 
                SELECT   dc.owner, 
                         st.tname, 
                         dcc.column_name cname, 
                         dc.constraint_type, 
                         dcc.position seqn,  
                         dc.r_owner, 
                         dc.constraint_name, 
                         dc.r_constraint_name, 
                         dc.delete_rule, 
                         dc.search_condition scondition, 
                         nvl(st.ck, 'X'), 
                         st.rowid 
                  FROM   sys.dba_constraints dc, sys.dba_cons_columns dcc, 
stab  st 
                 WHERE  dcc.table_name = dc.table_name 
                   AND  dcc.owner = dc.owner 
                   AND  dcc.constraint_name = dc.constraint_name 
                   AND  dcc.table_name = st.tname 
                   AND  dcc.column_name = st.cname 
                   AND  st.ouser        = user||userenv('terminal') 
                   AND  dc.status = 'ENABLED'; 
 
        ownr     varchar2(30); 
        tname    varchar2(30); 
        coln     varchar2(30); 
        ctype    char(1); 
        seqn     varchar2(3); 
        rowner   varchar2(30); 
        constn   varchar2(30); 
        rconsts  varchar2(30); 
        refer    varchar2(2000); 
        rdesp    varchar2(2000); 
        drule    varchar2(9); 
        nulltype varchar2(3); 
        rc       number := 0; 
        rid      varchar2(20); 
 
    -- Executable Block 
---------------------------------------------------------------------------- 
BEGIN 
        OPEN consts; 
        LOOP 
                FETCH consts INTO ownr, tname, coln , ctype,seqn,  rowner, 
constn, rconsts,  drule, refer, nulltype, rid; 
                EXIT  WHEN  consts%NOTFOUND; 
 
                IF    ctype = 'P' THEN 
                                UPDATE stab set pk  = ctype||seqn, delete_rule 
= drule 
                                 WHERE rowid = rid; 
                ELSIF ctype = 'U' THEN 
                                UPDATE stab set uq  = ctype||seqn, delete_rule 
= drule 
                                 WHERE rowid = rid; 
                ELSIF ctype = 'C' THEN 
                            IF nulltype != 'C' THEN 
                                UPDATE stab set ck  = ctype, condition = refer 
                                 WHERE rowid = rid; 
                            END IF; 
                ELSIF ctype = 'R' THEN 
 
                        SELECT  '->'||dc.owner ||'.'|| 
dc.table_name||'.'||dcc.column_name   
                         INTO rdesp 
                          FROM   sys.dba_constraints dc, sys.dba_cons_columns 
dcc 
                        WHERE  dc.owner   = rowner 
                          AND  dcc.owner = dc.owner 
                          AND  dcc.table_name = dc.table_name 
                          AND  dcc.constraint_name = dc.constraint_name 
                          AND  dc.constraint_name = rconsts 
                          AND  dcc.position       = seqn 
                          AND  dc.constraint_name in ( SELECT r_constraint_name 
                                        FROM   sys.dba_constraints c, 
sys.dba_cons_columns d 
                                        WHERE  c.owner      = d.owner 
                                        AND    c.table_name = tname 
                                        AND    c.table_name = d.table_name 
                                        AND    c.constraint_name = constn 
                                        AND    c.constraint_name = 
d.constraint_name 
                                        AND    c.r_constraint_name = rconsts 
                                        AND    d.column_name = coln ); 
 
                                UPDATE stab set condition = rdesp, fk = ctype, 
delete_rule = drule 
                                 WHERE rowid = rid; 
                ELSE 
                        null; 
                END IF; 
        END LOOP; 
        CLOSE consts; 
        COMMIT; 
END; 
 
/ 
 
 
clear screen 
 
set pause on 
set pause 'Hit Return Gently ...' 
 
set linesize 132 
set pagesize 4ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ   heading 'Delete|Rule' 
 column own         format  a10  heading 'Object|Owner' 
column owner       noprin 
 
SELECT owner, cname , nulltype, type, pk, fk, uq , ck, Condition scond , 
       delete_rule delrule, owner own 
 FROM  stab  
WHERE  ouser = user||userenv('terminal') 
ORDER BY seq; 
 
set define off 
set feedback on 
set verify on 
ttitle off