Procédure SQL (pour envoi mail)

**Hyena77** · 26/01/2023, 11h41

Bonjour,

J'aurais besoin de conseils, je souhaiterais envoyer par mail le résultat de ma requête uniquement si mon flag de ma requête est égal à 1.
Je n'arrive pas à savoir où rajouter ma condition, de plus, malheureusement, j'ai une erreur de compilation que je ne comprends pas

Elément Procedure SEND_MAIL compilé

LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: champs en double interdits dans RECORD ou TABLE
Erreurs : consulter le journal du compilateur

Je vous mets mon code:

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
CREATE OR REPLACE PROCEDURE send_mail (v_mail_to        IN VARCHAR2,
                                       v_mail_from      IN VARCHAR2,
                                       v_mail_body      IN VARCHAR2,
									   v_mail_subject   IN VARCHAR2,
                                       v_mail_smtp_host IN VARCHAR2,
                                       v_mail_smtp_port IN NUMBER DEFAULT 25)
IS
v_mail_conn  utl_smtp.connection;
v_mail_from VARCHAR2(255):='toto@ttt.com';
v_mail_to VARCHAR2(255):='toto@ttt.com';
v_mail_subject VARCHAR2(255):='Etat des tablespace';
v_mail_body VARCHAR2(4000) := 'select T1.tablespace_name as "Nom_tablespace",
T1.bytes / 1024 / 1024 as "Espace_utilisé (Mb)",
T2.bytes / 1024 / 1024 as "Espace_libre (Mb)",
T2.largest /1024 /1024 as "Largest (Mb)",
round(((T1.bytes-T2.bytes)/T1.bytes)*100,2) as "Pourcentage_utilisé",
case
when round(((T1.bytes-T2.bytes)/T1.bytes)*100,2) >= 83 
then 1 
else 
0 
end 
flag_envoi_mail
from
(
select tablespace_name,
sum(bytes) as bytes
from dba_data_files
group by tablespace_name
)
T1,
(
select tablespace_name,
sum(bytes) as bytes ,
max(bytes) as largest
from dba_free_space
group by tablespace_name
)
T2
where T1.tablespace_name=T2.tablespace_name
order by ((T1.bytes-T2.bytes)/T1.bytes) desc ;';
BEGIN 
utl_smtp.helo(v_mail_conn, 'domaine.fr');
utl_smtp.mail(v_mail_conn, v_mail_from);
utl_smtp.rcpt(v_mail_conn, v_mail_to);
utl_smtp.open_data(v_mail_conn);
utl_smtp.write_data(v_mail_conn, 'Subject: ' || v_mail_subject || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'From: ' || v_mail_from || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'To: ' || v_mail_to || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, v_mail_body || utl_tcp.crlf);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_ErrorORutl_smtp.Permanent_Error then
raise_application_error(-20000,'Unabletosendmail',TRUE);
END;
/

Vous remerciant par avance de votre aide

**JeitEmgie** · 27/01/2023, 11h05

Vous ne pouvez pas avoir des variables locales qui portent le même nom que des paramètres.

**Hyena77** · 27/01/2023, 14h27

Merci pour votre réponse en creusant j'ai compris que c'était cela.

J'ai essayé une autre approche qui est :
- Créer d'abord la procédure lclassique

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
 
 CREATE OR REPLACE PROCEDURE send_mail (v_mail_to       IN VARCHAR2,
                                        v_body          IN VARCHAR2,
                                       v_mail_from      IN VARCHAR2,
									   v_mail_subject   IN VARCHAR2,
                                       v_mail_smtp_host IN VARCHAR2,
                                       v_mail_smtp_port IN NUMBER DEFAULT 25)
AS
v_mail_conn utl_smtp.connection;
 
BEGIN
v_mail_conn := utl_smtp.open_connection(host => 'smtp.domaine.fr', port => 25);
utl_smtp.helo(v_mail_conn, 'domaine.fr');
utl_smtp.mail(v_mail_conn, v_mail_from);
utl_smtp.rcpt(v_mail_conn, v_mail_to);
utl_smtp.open_data(v_mail_conn);
utl_smtp.write_data(v_mail_conn, 'Subject: ' || v_mail_subject || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'From: ' || v_mail_from || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'To: ' || v_mail_to || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, v_mail_body);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
END;
/

- Appeler ensuite la procédure avec les paramètres

Code :

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

1
2
3
4
5
6
7
8
9
10
 
BEGIN send_mail (
v_mail_from => 'toto@gmail.com',
v_mail_to => 'toto@gmail.com',
v_mail_subject => 'Liste des tablespace',
v_mail_smtp_host => 'smtp.domaine.fr',
v_mail_smtp_port => 25,
v_body => 'Résultat de la requete');
END;
/

Ma procédure et mon appel fonctionne, le mail est bien reçu.

Problème je n'arrive pas a exécuter mon select dans le code de ma procédure.
Quand je rajoute il n' y a plus rien qui focntionne

Je n'arrive pas à stocker correctement le résultat et donc à l'afficher dans le corps du mail

Bref je n'arrive plus à rien
Help me

**vanagreg** · 27/01/2023, 16h56

Bonjour,

Tu reçois la requête elle-même, pas son résultat. Le mieux est que tu stocke le résultat dans un CLOB et que tu mettes ce CLOB en pièce jointe du mail.

**JeitEmgie** · 28/01/2023, 10h19

Est-ce que vous avez seulement relu ce que vous avez écrit?

Vous faites une boucle dans laquelle vous assignez à une variable un des paramètres de votre procédure :
donc comment voulez-vous que cette boucle serve à quelque chose en rapport avec le query ?

Sans parler de ';' dans le order by qui devrait générer une erreur de syntaxe : ce code ne devrait même pas compiler...

**Hyena77** · 31/01/2023, 11h33

Bonjour,

J'ai pris en compte toutes vos remarques, en me basant sur le site oracle j'ai refait tout propre.
Je suis parti sur la proposition de vanagreg et j'ai réussi à envoyer un mail avec PJ

Pour cela j'ai créer une procédure pour envoyer mail.
Un bloc de spool pour récuperer le résultat dans un fichier .csv

Pui j'ai créé son appel via le code simple suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
  l_clob CLOB := 'test';
BEGIN
send_mail_bis_bis(p_to          => 'toto@gmail.com',
          p_from        => 'toto@gmail.com',
          p_subject     => 'Etat TableSpace',
          p_text_msg    => 'Résultat de la requête sur l état des tablespaces',
          p_attach_name => 'Tablespace.csv',
          p_attach_mime => '/home/oracle/test_pj',
          p_attach_clob => l_clob,
          p_smtp_host   => 'smtp.domaine.fr');
END;
/

Par contre je n'arrive pas à bien mettre la PJ, à savoir le fichier .csv dans le mail

Merci à tous par avance.

Procédure SQL (pour envoi mail) [19c]

PL/SQL Oracle

Discussions similaires

Partager

Partager