Discussion :

[Wurlitzer]

Bonjour à tous,

Je cherche à écrire une requête qui puisse me donner la liste des CHECK CONSTRAINT d'une table

Je suis donc parti sur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT constraint_name, search_condition
  FROM dba_constraints
 WHERE owner = 'MDSYS' 
   AND table_name = 'MD$DIM'
   AND constraint_type ='C'

qui donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CONSTRAINT_NAME	SEARCH_CONDITION
 
MD17	"CNAME" IS NOT NULL
MD18A	"DIM#" IS NOT NULL
MD18B	dim#>0 AND dim#<=32
MD19A	"NAME" IS NOT NULL
MD19B	name = upper(name)            AND            length(name) <= 19
MD20	"LB" IS NOT NULL
MD21	"UB" IS NOT NULL
MD22A	"SCALE" IS NOT NULL
MD22B	scale >= 0
MD23A	"LEV" IS NOT NULL
MD23B	lev >  0

le problème est que je veux faire la distinction entre les CHECK et les NOT NULL et que cette information est dans la colonne SEARCH_CONDITION qui est un LONG.

Je ne peux donc pas ajouter de condition du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
AND search_condition NOT LIKE '%IS NOT NULL'

Histoire de compliquer un peu les choses. Je suis en 8.1.7.4 et je dois pouvoir faire cette requête a partir d'un user DBA différent de SYS. Je ne peux donc pas faire un bout de code PL/SQL qui lit le LONG dans la table DBA_CONSTRAINTS puisque le droit d'exécution sur cette table provient du rôle DBA et je ne peux pas l'attribuer directement a mon utilisateur

Si vous avez des idées je suis preneur...

[pifor]

Réponse en anglais de Tom Kyte http://asktom.oracle.com/pls/ask/f?p...A:839298816582

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ops$tkyte@ORA920LAP> create or replace function get_search_condition( 
p_cons_name in varchar2 ) return varchar2
  2  authid current_user
  3  is
  4      l_search_condition user_constraints.search_condition%type;
  5  begin
  6      select search_condition into l_search_condition
  7        from user_constraints
  8       where constraint_name = p_cons_name;
  9
 10      return l_search_condition;
 11  end;
 12  /
 
Function created.
 
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select constraint_name
  2    from user_constraints
  3   where get_search_condition(constraint_name) like '%NOT NULL%';
 
CONSTRAINT_NAME
------------------------------
SYS_C004792
SYS_C004794
SYS_C004181
....

[Wurlitzer]

Merci, mais cela ne me va pas tout a fait.

D'abord parce que je dois pouvoir faire mes recherches sur toutes les contraintes de tous les utilisateurs. C'est a dire utiliser la table DBA_CONSTRAINTS et non pas seulement USER_CONSTRAINTS. Or je peux accéder a cette table en SQL (droit par le rôle DBA) mais pas PL/SQL (pas de droit en direct et je ne peux pas en donner).

Ensuite parce que l'exécution de cette fonction très très lente mais bon la c'est peut être du a ma base de test qui n'est pas optimisé.

J'ai fini par trouver une solution qui même si elle n'est pas très jolie répond a mes "contraintes" :

En passant par une table intermédiaire et un champs LOB

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE TABLE CKC_CONSTRAINT ( owner                VARCHAR2(30),
                                                object_name       VARCHAR2(30),
		     			        constraint_name  VARCHAR2(30),
						last_ddl_time    DATE,
  						search_condition CLOB);
 
INSERT INTO ckc_constraint
   SELECT ao.owner, ao.object_name, dc.constraint_name, ao.last_ddl_time,
         TO_LOB (dc.search_condition)
     FROM all_objects ao, dba_constraints dc
    WHERE dc.constraint_type = 'C'
      AND ao.owner IN ('SCH1', 'SCH2')
      AND dc.table_name = ao.object_name
      AND dc.owner = ao.owner;
 
SELECT owner, object_name, constraint_name,last_ddl_time, search_condition 
FROM dx_ckc_constraint
where dbms_lob.instr(search_condition, 'IS NOT NULL') = 0
ORDER BY last_ddl_time DESC, owner, object_name 
 
DROP TABLE DX_CKC_CONSTRAINT;

[laurentschneider]

les contraintes not null, c'est des contraintes CHECK.

Pour savoir quelles colonnes ne peuvent pas être nul, emploie simplement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select table_name,column_name from user_tab_columns where nullable='N';

Tu obtiendras toutes les colonnes CLE PRIMAIRE et CHECK IS NOT NULL

A+
Laurent

[Wurlitzer]

Merci, mais je veux juste l'inverse....

C'est a dire les CONSTRAINTS CHECK qui ne sont pas des NOT NULL

[LeoAnderson]

not exist est donc votre ami !