Bonjour a tous,

Voici mon probleme. Une procedure SET_INV dont les tests sous PL/SQL Developer ne revelent aucun probleme :

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
PROCEDURE SET_INV (
       V_ROLLOUT_CODE  IN   INTEGER,
       V_PLANNED       IN   VARCHAR2,
       V_PLANNED_YEAR  IN   INTEGER,
       V_PLANNED_MONTH IN   INTEGER,
       V_REMARKS       IN   VARCHAR2,
       V_RESPONSE_ID   OUT  INTEGER,
       V_RESPONSE_DESC OUT  VARCHAR2
/* Parameters description :
** 1. V_ROLLOUT_CODE:  a. record Code in the rollout_inv table
**                     b. mandatory parameter
**                     c. MUST already exist ! (No insert allowed into rollout_inv, only updates)
**
** 1bis. V_PLANNED:    a. mandatory !
                       b. (YES, NO) -> (1,0) : 0 = not planned => V_REMARKS mandatory
                                             1 = planned => V_PLANNED_YEAR mandatory
**
** 2. V_PLANNED_YEAR:  a. if the status of the record (designed by its code) in the rollout table
**                         is 'PLANNED', then this parameter is mandatory
**                     b. MUST BE equal or higher than current year
**
** 3. V_PLANNED_MONTH: a. not mandatory
**                     b. must be in [1..12]
**
** 4. V_REMARKS:       a. remarks on planification : NEEDED IF the status of 2.a is different from 'PLANNED'
**                     b. must be < 100 characters
**
** 5. V_RESPONSE_ID:   a. 0 = all went OK
                          1 = validation problem : one or more of the previous points are not satisfied
                          2 = DB problem
** 6. V_RESPONSE_DESC: a. Description of the response (complementary of above code)
                       b. must be < 200 characters
** End of paramters description */
 
) IS
 
-- Invalid parameters exception
V_VALIDATION_ERROR EXCEPTION;
-- Checking point 1.c we will count the number of records with code=V_ROLLOUT_CODE and put the result into this variable
V_VALID_CODE INTEGER:=0;
 
BEGIN
/* Validation tests */
   -- 1.b
   IF (V_ROLLOUT_CODE IS NULL) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'Parameter RolloutCODE is mandatory !';
        RAISE V_VALIDATION_ERROR;
   END IF;
 
   -- 1.c
   SELECT COUNT(*) INTO V_VALID_CODE FROM SMART.V_ROLLOUT_INV WHERE V_ROLLOUT_INV.CODE = V_ROLLOUT_CODE;
   IF(V_VALID_CODE<>1) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'There is no record with code '||V_ROLLOUT_CODE||' in the Rollout_INV table !';
        RAISE V_VALIDATION_ERROR;
   END IF;
 
   -- 1bis.a
   IF (V_PLANNED IS NULL) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'Parameters V_PLANNED is mandatory !';
        RAISE V_VALIDATION_ERROR;
   END IF;
   -- 1bis.b (V_PLANNED other than 'YES' or 'NO')
   IF (V_PLANNED <> 'YES') AND (V_PLANNED <> 'NO') THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'Parameters V_PLANNED is not recognized : must be YES or NO !';
        RAISE V_VALIDATION_ERROR;
   END IF;
 
   -- 2.a
   IF(V_PLANNED = 'YES') AND (V_PLANNED_YEAR IS NULL) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'The status of the record with code '||V_ROLLOUT_CODE||' is PLANNED : PLANNED_YEAR argument is mandatory !';
        RAISE V_VALIDATION_ERROR; 
   END IF;
   -- 2.b
   IF(V_PLANNED_YEAR < extract(year from sysdate)) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'The input PLANNED_YEAR for the record with code '||V_ROLLOUT_CODE||' must be equal or higher than '||extract(year from sysdate)||'!';
        RAISE V_VALIDATION_ERROR; 
   END IF;
 
   --3.b
   IF(V_PLANNED_MONTH IS NOT NULL) THEN
        IF (V_PLANNED_MONTH < 1) OR (V_PLANNED_MONTH>12) THEN
           V_RESPONSE_ID := 1;
           V_RESPONSE_DESC := 'The input PLANNED_MONTH for the record with code '||V_ROLLOUT_CODE||' must be in [1..12]!';
           RAISE V_VALIDATION_ERROR;
        END IF;
   END IF;
 
   --4.a
    IF(V_PLANNED = 'NO') AND (V_REMARKS IS NULL) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'The status of the record with code '||V_ROLLOUT_CODE||' is not PLANNED : REMARKS argument is mandatory !';
        RAISE V_VALIDATION_ERROR; 
   END IF;
   --4.b
   V_VALID_CODE := LENGTH(V_REMARKS);
   IF(V_REMARKS IS NOT NULL) AND (LENGTH(V_REMARKS)>100) THEN
        V_RESPONSE_ID := 1;
        V_RESPONSE_DESC := 'For the record with code '||V_ROLLOUT_CODE||', the argument REMARKS must be < to 100 characters !';
        RAISE V_VALIDATION_ERROR; 
   END IF;
/* End of validation tests */
 
   -- Update
   UPDATE V_ROLLOUT_INV 
   SET MONTH = V_PLANNED_MONTH, YEAR = V_PLANNED_YEAR, REMARKS = V_REMARKS
   WHERE CODE = V_ROLLOUT_CODE;
   -- Committing updates
   COMMIT;
   -- Output parameters setting
   V_RESPONSE_ID := 0;
   V_RESPONSE_DESC := 'The record with code '||V_ROLLOUT_CODE||' was updated properly.';
   DBMS_OUTPUT.put_line('Successful update ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
 
EXCEPTION
   WHEN V_VALIDATION_ERROR THEN
        DBMS_OUTPUT.put_line('Validation Error ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
        ROLLBACK;
   WHEN OTHERS THEN
        V_RESPONSE_ID := 2;
        V_RESPONSE_DESC := 'For the record with code '||V_ROLLOUT_CODE||' : '||SUBSTR(SQLERRM, 1, 200);
        DBMS_OUTPUT.put_line('Procedure Error ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
        ROLLBACK;
        RAISE;
 
 
END SET_INV;
Lors d'une execution de cette procedure a partir d'une application externe, les utilisateurs obtiennent neanmoins l'erreur suivante :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
XXXXX.XXXXXX.SET_INV
Exception: ORA-06550: line 1, column 42:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
   . ( * @ % & = - + ; < / > at in is mod not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like
   between || indicator
ORA-06550: line 1, column 216:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
   . ( , * % & = - + < / > at in is mod not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like alter
   between || invalidate cascade
Je ne sais pas encore exactement si l'application externe qui appelle cette procedure est sur plateforme .NET ou J2EE. Sauriez-vous pourquoi un appel de l'exterieur pourrait fausser le fonctionnement de la procedure et renvoyer l'erreur ci-dessus ?

Merci d'avance !