[debutant][Script] Erreur SQL

**pmartin8** · 15/08/2006, 16h54

Voici mon script:

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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
DECLARE
 
 cur_not_used_type  MENU_TYPE%ROWTYPE;
 cur_gen_menu       GENERIC_MENU%ROWTYPE;
 cur_option_menu	OPTION_MENU%ROWTYPE;
 
 tmp_gen_menu  		GENERIC_MENU%ROWTYPE;
 
BEGIN
 
	 BEGIN  	 
		 ALTER TABLE  MENU_TYPE 
 		  DROP COLUMN MENU_ORDER;
 
 		 ALTER TABLE MENU_TYPE
   		  DROP COLUMN INDIC_VISIBLE;
 
	 EXCEPTION WHEN OTHERS THEN
	   DBMS_OUTPUT.PUT_LINE('Column already dropped');
	 END;
 
	 DELETE FROM OPTION_MENU
	 WHERE TYPE_ID NOT IN ('FPI','BPI',
					       'CAR','SHO','SOF',
						   'ARR','ADO',
						   'RMO','RMR','IRR',
						   'UHF','TRO','HOW',
						   'IDI','KNI','TII');
 
	--These generic menu must be deleted				   
	FOR cur_gen_menu IN (SELECT * FROM WHERE TYPE_ID NOT IN ('DES','REM','TRO'))
	LOOP
 
	  --manage the option menu
	  FOR cur_option_menu IN (SELECT * FROM OPTION_MENU WHERE GENERIC_MENU_ID = cur_gen_menu.GENERIC_MENU_ID)
	  LOOP
 
		 IF cur_option_menu.TYPE_ID = 'FPI' OR 
		    cur_option_menu.TYPE_ID = 'BPI' OR
		    cur_option_menu.TYPE_ID = 'CAR' OR
			cur_option_menu.TYPE_ID = 'SHO' OR
			cur_option_menu.TYPE_ID = 'SOF'
		 THEN
		   BEGIN
		      SELECT *
			    INTO tmp_gen_menu
			    FROM GENERIC_MENU
			   WHERE TYPE_ID = 'DES'
			     AND MODEL_ID = cur_gen_menu.MODEL_ID;
 
			  UPDATE OPTION_MENU
			  	 SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
               WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
 
		   EXCEPTION WHEN NO_DATA_FOUND THEN
		      DBMS_OUTPUT.PUT_LINE('No DES Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
		   END;
 
		 ELSIF cur_option_menu.TYPE_ID = 'ARR' OR
		       cur_option_menu.TYPE_ID = 'ADO' OR
			   cur_option_menu.TYPE_ID = 'RMO' OR
			   cur_option_menu.TYPE_ID = 'RMR' OR
			   cur_option_menu.TYPE_ID = 'IRR' OR
			   cur_option_menu.TYPE_ID = 'UHF' 
		 THEN 
		   BEGIN
		      SELECT *
			    INTO tmp_gen_menu
			    FROM GENERIC_MENU
			   WHERE TYPE_ID = 'REM'
			     AND MODEL_ID = cur_gen_menu.MODEL_ID;
 
			  UPDATE OPTION_MENU
			  	 SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
               WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
 
		   EXCEPTION WHEN NO_DATA_FOUND THEN
		      DBMS_OUTPUT.PUT_LINE('No REM Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
		   END;
 
		 ELSIF cur_option_menu.TYPE_ID = 'TRO' OR
		       cur_option_menu.TYPE_ID = 'HOW' OR
			   cur_option_menu.TYPE_ID = 'IDI' OR
			   cur_option_menu.TYPE_ID = 'KNI' OR
			   cur_option_menu.TYPE_ID = 'TII'
		 THEN 
		 	BEGIN
		      SELECT *
			    INTO tmp_gen_menu
			    FROM GENERIC_MENU
			   WHERE TYPE_ID = 'TRO'
			     AND MODEL_ID = cur_gen_menu.MODEL_ID;
 
			  UPDATE OPTION_MENU
			  	 SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
               WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
 
		   EXCEPTION WHEN NO_DATA_FOUND THEN
		      DBMS_OUTPUT.PUT_LINE('No TRO Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
		   END; 
		 END IF;
	  END LOOP;
 
	END LOOP;
 
	DELETE FROM GENERIC_MENU
	 WHERE TYPE_ID NOT IN ('DES','REM','TRO');
 
	DELETE FROM MENU_TYPE
	 WHERE TYPE_ID NOT IN  ('FPI','BPI','DES',
					       'CAR','SHO','SOF',
						   'ARR','ADO','REM',
						   'RMO','RMR','IRR',
						   'UHF','TRO','HOW',
						   'IDI','KNI','TII');
 
 
END;

J'ai l'erreur suivant

Code :

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

1
2
3
4
5
6
7
8
9
10
 
ORA-06550: line 12, column 4:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
 
   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge
   <a single-quoted SQL string> pipe