Collection dans corps de message

**olivanto** · 04/12/2008, 11h03

je transfert mon message depuis le sous forum admin...

J'ai besoin de faire tourner une procédure, qui va régulièrement me donner des nouvelles de ma (mes) base (s), frequemment modifiée(s) par des intervenants sans scrupules (mes collègues

)

Comme je suis pas une lumière en PL/SQL, j'ai évidemment un problème que je ne comprend pas; j'ai entre autre dans le code un curseur qui me liste une collection de contraintes désactivées, que je place dans un type défini.

Sauf que ce type défini, je ne sais pas le ressortir pour que ma liste s'intègre dans le corps de mon message...

Voilà le code. La variable de mon type s'appelle "mescontraintes", et lorsque j'essaie de l'utiliser, ma procédure plante (ligne 53)

Comment fait-on ??

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
 
CREATE OR REPLACE PROCEDUREsys_control 
IS
 
-- Procédure controle BASE , envoi par mail--
TYPE tableau_contraintes IS TABLE OF VARCHAR2(150);
 
jobs81 varchar2(70);
jobs42 varchar2(70);
jobs2 varchar2(70);
corps varchar2(9000);
mescontraintes tableau_contraintes;
objet varchar2(20) := 'controle BD PROD';
mail1 varchar2(40) := '<moi@mapomme.fr>';
de varchar2(40) := '<moi@mapomme.fr>';
 
Cursor C_con
IS
select constraint_name || '/ ' || table_name "contraintes_desactivees"
from user_constraints  where status like 'DISABLED';
 
 
BEGIN
 
 open C_con;
    fetch C_con bulk collect into mescontraintes;
    Close C_con;
 
 
select decode(broken,'N','okay' || ' [ ' || next_date || ' ]','planté !!!!!') 
--|| what || '(' ||  job || ')' || ' --> ' || next_date || ' -midi' 
into jobs81 from DBA_JOBS where job = 81; 
 
select decode(broken,'N','okay' || ' [ ' || next_date || ' ]','planté !!!!!') 
--|| what || '(' ||  job || ')' || ' --> ' || next_date  
into jobs42 from DBA_JOBS where job = 42; 
 
select decode(broken,'N','okay' || ' [ ' || next_date || ' ]','planté !!!!!') 
--|| what || '(' ||  job || ')' || ' --> ' || next_date  
into jobs2 from DBA_JOBS where job = 2; 
 
corps := '<?xml version="1.0" encoding="iso-8859-15"?>' 
           || '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'
         || '<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr">'
           || '<head>'
         || '</head>'
         || '<body>'
           || sysdate || '<br/>'
          || 'JOBS (N°) : état & prochaine exécution ' || '<br/>'
         || 'DBMS_STATS_GATHER_SCHEMA_STATS(USRIHF)   (2) : ' || jobs2 || '<br/>'
         || 'mise à jour des FOREIGN KEYS (42) : ' || jobs42 || '<br/>'
         || 'mise à jour des comptes midi (81) : ' || jobs81 || '<br/>'
         || '<br/>'
     --    || 'CONTRAINTES DESACTIVEES ' ||  mescontraintes     || '<br/>'
         || '<br/>'
         || '</body>'
         || '</html>' ;
 
        send_mail_4(de,mail1,objet,corps);
 
 
 
 
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
 
 
END sys_control;
/

**SheikYerbouti** · 04/12/2008, 11h27

et lorsque j'essaie de l'utiliser, ma procédure plante (ligne 57)

Ah oui, et il faut compter les lignes et deviner l'erreur ?

**olivanto** · 04/12/2008, 11h50

hum...

c'est la seule ligne en commentaire dans le corps du message, là où j'ai écrit en majuscule "CONTRAINTE DESACTIVEES".

Pas besoin de deviner l'erreur, je l'explique assez clairement, non ? Ce que je ne sais pas, c'est résoudre le problème.

Je suis pas doué, hein ?

**fatsora** · 04/12/2008, 13h32

en fait ta procedure renvoie un message ORA --
PLS --

peux tu nous envoyer la ligne qui correspond STP ?

**SheikYerbouti** · 04/12/2008, 13h42

Envoyé par olivanto

hum...

c'est la seule ligne en commentaire dans le corps du message, là où j'ai écrit en majuscule "CONTRAINTE DESACTIVEES".

ça fait beaucoup de chose à deviner.

Envoyé par olivanto

Pas besoin de deviner l'erreur, je l'explique assez clairement, non ?

Effectivement, votre explication se résume à : "ça plante"

Si vous voulez que l'on vous aide, prenez au moins la peine de donner l'erreur exacte. On n'a pas que ça à faire...

**olivanto** · 04/12/2008, 13h59

en effet. Je suis pas clair. Je suis dessus depuis plusieurs heures et cela me paraît évident...dsl!

En fait, lorsque je compile ma fonction telle quelle, avec cette fameuse ligne en commentaire, elle fonctionne très bien, excepté bien sûr l'élément se rapportant à cette ligne. Je reçois bien mon mail, et tout et tout.

Quand je passe cette ligne "en actif", j'ai cette erreur ;

Code :

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

1
2
3
 
(27;10) ligne de début de corps
Oracle Error PLS-00306 ; numéro ou type d'argument érroné dans appel à '||'

Je suppose donc que mon "objet" nommé mescontraintes est foireux à cet endroit, mais je ne sais pas comment l'utiliser dans ce genre de cas...

Suis je plus clair (où tout du moins, moins ténébreux) ?

PS :
Je remets le code, avec uniquement ce qui nous concerne et ne fonctionne pas.

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
 
CREATE OR REPLACE PROCEDURE sys_control 
IS
 
-- Procédure controle BASE , envoi par mail--
TYPE tableau_contraintes IS TABLE OF VARCHAR2(150);
 
mescontraintes tableau_contraintes;
 
corps varchar2(9000);
objet varchar2(20) := 'controle BD PROD';
mail1 varchar2(40) := '<moi@mapomme.fr>';
de varchar2(40) := '<moi@mapomme.fr>';
 
Cursor C_con
IS
SELECT constraint_name || '/ ' || table_name "contraintes_desactivees"
FROM user_constraints  WHERE STATUS LIKE 'DISABLED';
 
 
BEGIN
 
 open C_con;
    fetch C_con bulk collect INTO mescontraintes;
    Close C_con;
 
 corps := '<?xml version="1.0" encoding="iso-8859-15"?>' 
           || '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'
         || '<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr">'
           || '<head>'
         || '</head>'
         || '<body>'
         || 'CONTRAINTES DESACTIVEES ' ||  mescontraintes     || '<br/>'
         || '<br/>'
         || '</body>'
         || '</html>' ;
 
        send_mail_4(de,mail1,objet,corps);
 
   EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
 
 END sys_control;

**brassouille** · 04/12/2008, 14h19

Ton objet "mescontraintes" est un tableau.
Or l'opérateur || ne sait concaténer que des strings.
C'est un peu comme si tu écrivais "i := 3 + toto"

Quand il s'agit par exemple d'un entier, on a une conversion implicite en string. Mais pour un tableau il n'y en a pas.

Il faut te créer une fonction toString(tableau_contraintes), qui va lire successivement les lignes du tableau et renvoyer une string résultant de la concaténation de toutes ces lignes. Puis remplacer ta ligne

Code :

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

|| mescontraintes ||

par

Code :

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

|| toString(mescontraintes) ||

**olivanto** · 04/12/2008, 14h44

Merci, t'es trop fort

.

Moins, en revanche, je suis pas prêt de faire tourner une focntion comme çà...
Quel type de paramètre d'entrée je dois mettre dans cette focntion ? Est ce que dois lui passer le contenu du curseur, ou le contenu de mon TYPE OF VARCHAR2 ???

Je vais m'y mettre de suite....

**plaineR** · 04/12/2008, 14h54

Sinon il y a plus simple : plutôt que te charger tes contraintes dans un tableau tu les charges directement dans une variable de type varchar2.

**brassouille** · 04/12/2008, 14h58

Ca va donner un truc de ce genre (pas testé)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
FUNCTION toString(tab tableau_contraintes) RETURN STRING IS
toReturn varchar2(20000) := '';
i BINARY_INTEGER;
BEGIN
  i := tab.FIRST;
  IF i IS NOT NULL THEN
    FOR i IN tab.FIRST..tab.LAST LOOP
      toReturn := toReturn || tab(i);
    END LOOP;
  END IF;
  RETURN toReturn;
END;

Ca marchera à condition que la taille total de la concaténation ne dépasse pas la taille maximale d'une variable de type VARCHAR2. Attention aussi à penser à augmenter la taille de ta variable "corps" en fonction.

Quand tu concatènes, tu peux aussi rajouter des virgules ou des <BR> entre chaque contrainte.

**brassouille** · 04/12/2008, 15h01

Envoyé par plaineR

Sinon il y a plus simple : plutôt que te charger tes contraintes dans un tableau tu les charges directement dans une variable de type varchar2.

Puisqu'il les charge via un curseur avec bulk collect, je pense qu'il est obligé d'utiliser un tableau, non?

**plaineR** · 04/12/2008, 15h02

Envoyé par brassouille

Puisqu'il les charge via un curseur avec bulk collect, je pense qu'il est obligé d'utiliser un tableau, non?

D'un autre côté, il n'est pas obligé de passer par un bulk collect

**brassouille** · 04/12/2008, 15h06

Envoyé par plaineR

D'un autre côté, il n'est pas obligé de passer par un bulk collect

Ah oui, parcourir le curseur et concaténer au fur et à mesure...
C'est vrai que ça fait un peu moins de code à écrire. Après c'est un peu moins performant mais pas grave vu la quantité de données

**plaineR** · 04/12/2008, 15h14

Envoyé par brassouille

Après c'est un peu moins performant mais pas grave vu la quantité de données

Pas sûr... Parce que là tu charges le tableau, et tu parcoures toutes les lignes de celui-ci. En plus il semble qu'à partir de la 10g, la différence de perf entre un bulk collect et un curseur "normal" soit très faible.

**olivanto** · 04/12/2008, 15h15

merci à tous. Un vrai plaisir !

Pour le bulk collect, j'ai utilisé ce que je connais, c'est tout ! S'il y a des moyens plus simple, je les ais pas utilisés par méconnaissance !
Toute fois la fonction tostring me servira bien pour la suite de ma procédure !

Une question à "brassouille" ;

Ta fonction à l'air simple une fois lue (c'est toujours comme çà), mais comment ma fonction peut elle connaitre l'objet "tab tableau_contraintes" en entrée, puisqu'il n'est connu que dans la procédure initiale sys_contrôle ?
Oracle me dit que "tableau_contraintes" doit être déclaré.

Est ce que je dois créer un package, avec ce "tab tableau_contraintes" en variable globale, connue par tout ce qui serait développé ensuite ??

**brassouille** · 04/12/2008, 15h23

Envoyé par plaineR

Pas sûr... Parce que là tu charges le tableau, et tu parcoures toutes les lignes de celui-ci. En plus il semble qu'à partir de la 10g, la différence de perf entre un bulk collect et un curseur "normal" soit très faible.

Je suis en 10g, j'ai cherché à optimiser une procédure très longue, et je t'assure que ça change du tout au tout

Sur certaines procédures je vais maintenant quasiment 10 fois plus vite en ayant simplement remplacé un parcours de curseur, par le remplissage d'un tableau intermédiaire suivi du parcours de ce tableau. Ca marche très très bien dans les cas sur lesquels j'ai travaillé, où on avait à chaque fois peu de lignes en retour, mais sur une procédure appelée très souvent.
Par contre sur de très grosses tables ça marche moins bien, je pense à cause du fait qu'on perd le traitement en parallèle de la requête du curseur, et de ce que tu fais à l'intérieur du parcours de ton curseur. Je sais qu'on peut aussi préciser combien de lignes on veut ramener à la fois avec le bulk collect, ça résoudrait peut-être ce problème mais je n'ai pas essayé.