Discussion :

[shmoolki]

BOnjour je creer une procedure avec a l interieur une autre procedure et je l appel et ca ne marche pas

VOici ma procedure
A Savoir que des que j enleve la procedure ERROR_LOG et bien entendu son appel tout fonctionne correctemeent

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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
create or replace 
PROCEDURE TMP_HANDLE_TLS_MISHKAL
IS
 
 
 
 
 
 CURSOR C_EMP IS
 SELECT * 
 FROM weight_conf 
 WHERE  NVL(datetime_conf,TO_DATE('01-01-1970','dd-mm-yyyy')) = TO_DATE('01-01-1970','dd-mm-yyyy')
;
 
 cur C_emp%ROWTYPE;
 
 
 CURSOR transp (transpid transports.transport_id%TYPE) IS
 SELECT * FROM transports WHERE  transport_id = transpid FOR UPDATE OF exit_weight, entry_weight;
 
 lt_trans transp%ROWTYPE;
 
 CURSOR wconftoday IS
 SELECT * FROM weight_conf WHERE datetime >= trunc(sysdate) ;
 
 cur_wconf wconftoday%ROWTYPE;
 
 CURSOR transp_today (transpid transports.transport_id%TYPE) IS
 select * from transports where entry_date > sysdate-1 AND transport_id = transpid FOR UPDATE OF sent_status_cd, sent_status_date;
 
 lt_transtoday transp_today%ROWTYPE;
 
 
 CURSOR tr_tosend IS
 SELECT * FROM transports 
 WHERE(sent_status_cd = -1 or sent_status_cd is null  or sent_status_cd = 0 or sent_status_cd=-2 ) 
 AND exit_date IS NULL and action_id<>2  and factory_id=2 and entry_date > sysdate-1 
 ORDER BY transports.transport_id;
 
 ls_tr_tosend tr_tosend%ROWTYPE;
 
 
 CURSOR car_by_licensno(trailerid transports.trailer_id%TYPE) IS
 select emptyweight,maxweight from cars where licensno = trailerid;
 
 ls_car_by_licensno car_by_licensno%ROWTYPE;
 
 PROCEDURE error_log (p_errorcode IN NUMBER, p_trans  IN NUMBER, p_trailer  IN NUMBER, p_errdescr IN VARCHAR2, p_errora IN VARCHAR2, p_errcom IN VARCHAR2) 
IS
 
 
 
  lv_sql NUMBER;
 
 
BEGIN  -- executable part starts here
 
 
 INSERT INTO Errors_log_a 
 (SEQ_ID, ERROR_CODE, TRANSPORT_ID, TRAILER_ID, DATETIMERR, ERROR_DESCR, ERROR_ORA, ERROR_COMMENT )
 values (
 lv_sql,
 p_errcode,
 p_trans,
 p_trailer,
 sysdate,
 p_errdescr,
 p_errora,
 p_errcom);
 
 
 
 
END error_log;
 
BEGIN
 
error_log(51,0,0,'-','-','TM_A Started');
 
 
 
Insert Into Weight_conf 
SELECT Weight.* 
FROM Weight, Weight_CONF , transports 
WHERE Weight.TRANSPORT_ID = Weight_CONF.TRANSPORT_ID (+)  
AND Weight_conf.TRANSPORT_ID IS NULL 
and weight.DateTime_W > sysdate - 1
AND transports.transport_id = weight.transport_id 
AND nvl(transports.exit_date,to_date('01-01-1970','dd-mm-yyyy')) = to_date('01-01-1970','dd-mm-yyyy');  
 
 
 
 OPEN C_EMP;
 
Loop
	FETCH C_EMP INTO Cur;
	dbms_output.put_line(c_EMP%ROWCOUNT);
	EXIT WHEN C_EMP%NOTFOUND;
 
	OPEN transp(cur.transport_id);
	FETCH transp INTO lt_trans;
	IF transp%NOTFOUND THEN
--	error_log(53, cur.transport_id, 0, 'No rec in Transports for rec in Weight_Conf', '-', 'tr_by_tid');
	dbms_output.put_line('test1');
	ELSE
	dbms_output.put_line('test2');
  UPDATE transports SET exit_weight= cur.weight_o, entry_weight= cur.weight_i WHERE CURRENT OF transp;
 
	END IF;
 
 
 
	CLOSE transp;
 
 
 
End loop ;
	 CLOSE C_EMP;
 
 
	UPDATE weight_conf SET dateTime_conf = sysdate WHERE datetime_conf IS NULL;
 
OPEN wconftoday;
Loop
	FETCH wconftoday INTO cur_wconf;
	dbms_output.put_line(wconftoday%ROWCOUNT);
	EXIT WHEN wconftoday%NOTFOUND;
 
	OPEN transp_today(cur_wconf.transport_id);
	FETCH transp_today INTO lt_transtoday ;
	IF transp_today%NOTFOUND THEN
--	error_log(54, wconftoday.transport_id, 0, 'No rec in Transports for rec in Weight_Conf', '-', 'tr_today');
	dbms_output.put_line('test1');
	ELSE
	dbms_output.put_line('test2');
		IF lt_transtoday.sent_status_cd NOT IN (0,1,2,3) THEN
		UPDATE transports SET sent_status_cd=0, sent_status_date = sysdate WHERE CURRENT OF transp_today;
		END IF;
	END IF;
 
 
 
	CLOSE transp_today;
 
 
 
End loop ;
	CLOSE wconftoday;
 
 
 
OPEN tr_tosend;
Loop
	FETCH tr_tosend INTO ls_tr_tosend;
	dbms_output.put_line(tr_tosend%ROWCOUNT);
	EXIT WHEN tr_tosend%NOTFOUND;
	OPEN car_by_licensno(ls_tr_tosend.trailer_id);
	FETCH car_by_licensno INTO ls_car_by_licensno;
 
 
 
 
	INSERT INTO weight_req (transport_id,trailer_id,weight_empty,weight_full,weight_max,flag_1,flag_2,datetime) 
	VALUES 
	(
	ls_tr_tosend.transport_id,
	ls_tr_tosend.trailer_id,
	NVL(ls_car_by_licensno.emptyweight,0),
	0,
	NVL(ls_car_by_licensno.maxweight,0),
	0,
	0,
	sysdate
	);
 
CLOSE car_by_licensno;	
 
End loop ;
	CLOSE tr_tosend;
 
 
 
 
 
 
--EXCEPTION 
--	WHEN OTHERS THEN RAISE;
 
 
 
 
 
 
 
END TMP_HANDLE_TLS_MISHKAL;

L erreur qui apparait c est Colon not allowed sur la variable lv_sql


Merci

Je galere depuis deux jours

Ps Le principale c est dans error_log c est la bas que ca bug
Mrci

[Baldrick]

Bonjour,

Je remarque que la variable lv_sql n'est jamais valorisée... C'est donc toujours NULL qu'on essaie d'insérer dans la colonne Seq_id de la table Errors_log_a.

Est-ce qu'il y a une contrainte NOT NULL ou PRIMARY KEY sur la colonne SEQ_ID de la table Errors_log_a ? Si oui (et vu le nom de la colonne, je crois que c'est probable), il est normal que cela déclenche une exception à l'exécution. Il faut simplement mettre une valeur adéquate dans cette variable.