Trigger DDL after alter or create

**fatsora** · 07/03/2008, 15h12

B'jour la liste,

J'essaie d'implementer un trigger after/before DDL

pour empecher de creer une table avec colonne number (defaut)
et mettre a la place number (n,v)

ex create t1 (col1 number(2,0));

voici

le code

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE OR REPLACE TRIGGER COL_NUM_ALTER
AFTER ALTER
ON SCHEMA
DECLARE
 v_precis user_tab_columns.data_precision%TYPE;
 v_scale  user_tab_columns.data_scale%TYPE;
v_data_type  user_tab_columns.data_type%TYPE;
 x user_tables.table_name%TYPE;
 sql_Text    ORA_NAME_LIST_T;
stmt varchar2(2000);
i pls_integer;
this_col varchar2(100);
 
BEGIN
 SELECT ora_dict_obj_name
  INTO x
  FROM dual;
  i:=ora_sql_txt(sql_text);
   if (ora_sysevent='ALTER') then
	for l in 1..i loop
	stmt := stmt || sql_text(l);
  end loop;
  this_col := REGEXP_SUBSTR(stmt,'[^[:blank:]]+', 1, 5);
 
  dbms_output.put_line('THIS  IS COLUMN :' ||this_col);
 
    for c in (SELECT data_precision,data_scale,data_type,column_name
    	FROM user_tab_columns
	where table_name=x
     and column_name=this_col) loop
 
  dbms_output.put_line('table_name :' ||x ||'- '||c.column_name);
 
	--  if c.column_name = trim(this_col) then
 
      dbms_output.put_line('STILL HAVE COL_NAME :' ||x ||'- '||c.column_name);
	     	IF (c.data_type='NUMBER' and c.data_precision is null ) THEN
    			RAISE_APPLICATION_ERROR(-20099, 'number precision must be not null');
  		  END IF;
  	--	  end if;
 end loop;
  end if;
--EXCEPTION
--when no_data_found then null;
END COL_NUM_ALTER;
/

Mais ca marche pas

Code :

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

1
2
3
4
5
6
7
8
9
alter table t3 add col1 number; ==> NE DEVRAIT PAS ETRE AUTORISE
THIS  IS COLUMN :col1
 
Table altered.
 
 alter table t3 add col3 number(3); ==> AUTORISE 
THIS  IS COLUMN :col3
 
Table altered.

Merci d'avance si quelqu'un peut m'aider

**orafrance** · 07/03/2008, 15h18

tout simplement :

Code :

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

1
2
3
4
SQL>alter table toto add (col1 number);
THIS  IS COLUMN :(col1
 
Table altered.

la colonne ne devrait pas être (col1 mais COL1 en majuscule est sans la parenthèse

**orafrance** · 07/03/2008, 15h28

tu peux aussi utiliser ora_is_alter_column. Par exemple :

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
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE TRIGGER COL_NUM_ALTER
AFTER ALTER
ON SCHEMA
DECLARE
 v_precis user_tab_columns.data_precision%TYPE;
 v_scale  user_tab_columns.data_scale%TYPE;
v_data_type  user_tab_columns.data_type%TYPE;
 x user_tables.table_name%TYPE;
 sql_Text    ORA_NAME_LIST_T;
stmt varchar2(2000);
i pls_integer;
this_col varchar2(100);
BEGIN
 SELECT ora_dict_obj_name
  INTO x
  FROM dual;
  i:=ora_sql_txt(sql_text);
   IF (ora_sysevent='ALTER') then
 FOR l IN 1..i loop
 stmt := stmt || sql_text(l);
  end loop;
  dbms_output.put_line(stmt);
  dbms_output.put_line('THIS  IS COLUMN :' ||this_col);
    FOR c IN (SELECT data_precision,data_scale,data_type,column_name
     FROM user_tab_columns
 WHERE table_name=x) loop
  dbms_output.put_line('table_name :' ||x ||'- '||c.column_name);
  if ora_is_alter_column(c.column_name) then
      dbms_output.put_line('STILL HAVE COL_NAME :' ||x ||'- '||c.column_name);
       IF (c.data_type='NUMBER' AND c.data_precision IS NULL ) THEN
       RAISE_APPLICATION_ERROR(-20099, 'number precision must be not null');
      END IF;
   end if;
 end loop;
  end IF;
--EXCEPTION
--when no_data_found then null;
END COL_NUM_ALTER;
/

malheureusement si ça fonctionne pour un MODIFY c'est pas le cas pour un ADD

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
SQL>alter table toto add (col2 number);
alter table toto add (col2 number)
THIS  IS COLUMN :
table_name :TOTO- COL1
 
Table altered.
 
SQL>alter table toto modify (col2 number);
alter table toto modify (col2 number)
THIS  IS COLUMN :
table_name :TOTO- COL1
table_name :TOTO- COL2
STILL HAVE COL_NAME :TOTO- COL2
alter table toto modify (col2 number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20099: number precision must be not null
ORA-06512: at line 28

**fatsora** · 07/03/2008, 16h43

POUr le MODIFY je oeux l'implementer

mais par contre

comment faire pour ne pas permettre le ADD number

comme ca alter table toto add col2 number;

qui devrait etre prohibe

moi j'ai essaie upper

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE OR REPLACE TRIGGER COL_NUM_ALTER
AFTER ALTER
ON SCHEMA
DECLARE
 v_precis user_tab_columns.data_precision%TYPE;
 v_scale  user_tab_columns.data_scale%TYPE;
v_data_type  user_tab_columns.data_type%TYPE;
 x user_tables.table_name%TYPE;
 sql_Text    ORA_NAME_LIST_T;
stmt varchar2(2000);
i pls_integer;
this_col varchar2(100);
 
BEGIN
 SELECT ora_dict_obj_name
  INTO x
  FROM dual;
  i:=ora_sql_txt(sql_text);
   if (ora_sysevent='ALTER') then
	for l in 1..i loop
	stmt := stmt || sql_text(l);
  end loop;
  this_col := upper(REGEXP_SUBSTR(stmt,'[^[:blank:]]+', 1, 5));
 
  dbms_output.put_line('THIS  IS COLUMN :' ||this_col);
 
    for c in (SELECT data_precision,data_scale,data_type,column_name
    	FROM user_tab_columns
	where table_name=x
     and column_name=this_col) loop
 
  dbms_output.put_line('table_name :' ||x ||'- '||c.column_name);
 
	--  if c.column_name = trim(this_col) then
 
      dbms_output.put_line('STILL HAVE COL_NAME :' ||x ||'- '||c.column_name);
	     	IF (c.data_type='NUMBER' and c.data_precision is null ) THEN
    			RAISE_APPLICATION_ERROR(-20099, 'number precision must be not null');
  		  END IF;
  	--	  end if;
 end loop;
  end if;
--EXCEPTION
--when no_data_found then null;
END COL_NUM_ALTER;
/

mais apparement il ne trouve pas

Code :

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

1
2
3
4
    for c in (SELECT data_precision,data_scale,data_type,column_name
    	FROM user_tab_columns
	where table_name=x
     and column_name=this_col) loop

OK ICI

Code :

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

1
2
3
4
alter table T5 add colabc number(3);
THIS  IS COLUMN :COLABC
 
Table altered.

MAIS LA ::: pAS OK

Code :

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

1
2
3
4
alter table T5 add col_2 number;  
THIS  IS COLUMN :COL_2
 
Table altered.

**pifor** · 09/03/2008, 19h54

En fait vous n'avez pas besoin d'ouvrir un curseur sur les colonnes de la table car vous ne pouvez pas avoir 2 colonnes qui ont le même nom dans la même table. Et vous pouvez simplifier votre code de la façon suivante:

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
SQL> 
SQL> set serveroutput on
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                
PL/SQL Release 10.2.0.1.0 - Production                                          
CORE    10.2.0.1.0      Production                                              
 
TNS for Linux: Version 10.2.0.1.0 - Production                                  
NLSRTL Version 10.2.0.1.0 - Production                                          
 
SQL> 
SQL> CREATE OR REPLACE TRIGGER COL_NUM_ALTER
  2  BEFORE ALTER
  3  ON SCHEMA
  4  DECLARE
  5   i int;
  6   sql_text    ORA_NAME_LIST_T;
  7   stmt varchar2(2000);
  8   this_col varchar2(100);
  9   this_ps varchar2(100);
 10  BEGIN
 11    i:=ora_sql_txt(sql_text);
 12    IF (ora_sysevent='ALTER') then
 13     FOR l IN 1..i
 14     LOOP
 15      stmt := stmt || sql_text(l);
 16     END LOOP;
 17     this_col := upper(REGEXP_SUBSTR(stmt,'[^[:blank:]]+', 1, 5));
 18     dbms_output.put_line('this_col:' ||this_col);
 19     this_ps := upper(REGEXP_SUBSTR(stmt,'[^[:blank:]]+', 1, 6));
 20     dbms_output.put_line('>' ||this_ps|| '<');
 21     IF ( REGEXP_LIKE(this_ps,'^\N\U\M\B\E\R.$'))
 22     THEN
 23      RAISE_APPLICATION_ERROR(-20099, 'number precision must be not null');
 24     END IF;
 25    END IF;
 26  END COL_NUM_ALTER;
 27  /
 
Declencheur cree.
 
SQL> show errors
Pas d''erreur.
SQL> 
SQL> drop table t;
 
Table supprimee.
 
SQL> 
SQL> create table t(c1 date);
 
Table creee.
 
SQL> alter table t add c2 number(3);
this_col:C2                                                                     
>NUMBER(3) <                                                                    
 
Table modifiee.
 
SQL> alter table t add c21 number (3);
this_col:C21                                                                    
>NUMBER<                                                                        
 
Table modifiee.
 
SQL> alter table t add c22 number ( 3);
this_col:C22                                                                    
>NUMBER<                                                                        
 
Table modifiee.
 
SQL> alter table t add c3 number(3,1);
this_col:C3                                                                     
>NUMBER(3,1) <                                                                  
 
Table modifiee.
 
SQL> alter table t add c4 varchar(10);
this_col:C4                                                                     
>VARCHAR(10) <                                                                  
 
Table modifiee.
 
SQL> alter table t add c5 number;
this_col:C5                                                                     
>NUMBER <                                                                       
alter table t add c5 number
*
ERREUR a la ligne 1 :
ORA-00604: une erreur s'est produite au niveau SQL recursif 1 
ORA-20099: number precision must be not null 
ORA-06512: a ligne 20

Mais attention la commande ALTER permet de modifier plusieurs colonnes à la fois:

Code :

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

1
2
3
4
5
6
 
SQL> alter table t add c6 number(2) add c7 number;
this_col:C6                                                                     
>NUMBER(2)<                                                                     
 
Table modifiee.

Sans compter les autres options

**fatsora** · 11/03/2008, 12h20

Merci a tous je pense que je vais me debrouiller avec tout ca

Trigger DDL after alter or create

Administration Oracle

Vue hybride

Discussions similaires

Partager

Partager