Discussion :

[Cofondinde]

Bonjour a tous,

Le script suivant crée un curseur et effectue des traitemants sur les lignes retournées. Seulemant je en parviens pas a le faire tourner. SQLPLus me retourne 92 et rien de plus. Quelqu'un a-t'il une idée ??

Merci d'avance

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
 
DECLARE
 
/* Declaration du curseur retournant tous les comptes de type SHARE*/
cursor c1 is 
SELECT	a.account,
       	a.descr,
	a.eff_status,
	a.account_type,
	a.ledtyp,
       	b.cptact,
       	b.cptpas,
       	b.cptactias,
       	b.cptpasias
FROM	tgl_act_tbl_syr a,
	tacxrefsyr b
WHERE	a.setid = 'SHARE' AND
	a.setid = b.setid AND
	a.ledtyp ='FRA'   AND
	a.account = b.account   ;
 
lv_account	tgl_act_tbl_syr.account%TYPE;
lv_descr	tgl_act_tbl_syr.descr%TYPE;
lv_eff_status	tgl_act_tbl_syr.eff_status%TYPE;
lv_account_type	tgl_act_tbl_syr.account_type%TYPE;
lv_ledtyp	tgl_act_tbl_syr.ledtyp%TYPE;
lv_cptact	tacxrefsyr.cptact%TYPE;
lv_cptpas	tacxrefsyr.cptpas%TYPE;
lv_cptactias	tacxrefsyr.cptactias%TYPE;
lv_cptpasias	tacxrefsyr.cptpasias%TYPE;
 
BEGIN
 
OPEN c1;
FETCH c1 INTO lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias;
 
LOOP
/*On sort du curseur quand il n'y a plus de lignes*/
EXIT WHEN c1%NOT FOUND;
 
 
/* Compte de bilan actif A */
IF (lv_account_type ='A' AND NOT(lv_cptact LIKE 'A%' AND lv_cptpas IS NULL AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
 
/* Compte de bilan passif C ou L */
ELSIF (lv_account_type = 'C' OR  lv_account_type = 'L') AND NOT(lv_cptact IS NULL AND lv_cptpas LIKE 'P%'AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
 
/* Compte de bilan mixte D */
ELSIF (lv_account_type ='D' AND NOT(lv_cptact LIKE 'A%' AND lv_cptpas LIKE 'P%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
 
/* Compte hors bilan actif H*/
ELSIF (lv_account_type ='H' AND NOT(lv_cptact LIKE 'H%' AND lv_cptpas IS NULL AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
 
/* Compte hors bilan passif I*/
ELSIF (lv_account_type ='I' AND NOT(lv_cptact IS NULL' AND lv_cptpas LIKE 'I%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
 
/* COmpte hors bilan mixte J*/
ELSIF lv_account_type ='J' AND NOT(lv_cptact LIKE 'H%' AND lv_cptpas LIKE 'I%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias); 
 
/* Compte de P&L de charge 'E'*/
ELSIF lv_account_type ='E' AND NOT(lv_cptact IS NULL AND lv_cptactias IS NULL AND (lv_cptpas LIKE '6%') AND (lv_cptpasias LIKE 'I6%' OR lv_cptpasias LIKE 'I7%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias); 
 
 
/* Compte de P&L de produit 'R'*/
ELSIF lv_account_type ='R' AND NOT(lv_cptact IS NULL AND lv_cptactias IS NULL AND (lv_cptpas LIKE '7%') AND (lv_cptpasias LIKE 'I6%' OR lv_cptpasias LIKE 'I7%')))
THEN 
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias); 
 
END IF;
 
FETCH c1 INTO lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias;
 
END LOOP;
 
CLOSE c1;
 
END;
 
SELECT * FROM t_ano_syr;
 
Exit;

[pifor]

Pour exécuter du PL/SQL avec Sql*Plus, il faut mettre un '/' après le code PL/SQL, sinon le code entré est uniquement mis dans le buffer Sql*Plus et rien n'est exécuté:

Code :

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

1
2
3
END;
/
SELECT * FROM t_ano_syr;