Dématérialisation - Services Numériques - nouvelles technologies

CREATE OR REPLACE PROCEDURE ENVOI_MAIL AS
BEGIN
DECLARE
   l_mailhost    VARCHAR2(64) := ’smtp.monrelais.fr’;
   l_from        VARCHAR2(64) := ‘moi@monrelais.fr’;
   l_subject     VARCHAR2(64) := ‘Un petit sujet’;
   l_to          VARCHAR2(32767) := ‘toi@monrelais.fr’;
   l_mail_conn   UTL_SMTP.connection;
   nbError       NUMBER(10) := 0;
   l_dest_tmp   VARCHAR2(32767);
BEGIN
   l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
   UTL_SMTP.helo(l_mail_conn, l_mailhost);
   UTL_SMTP.mail(l_mail_conn, l_from);
   WHILE INSTR(l_to, ‘,’) != 0 LOOP
      l_dest_tmp := SUBSTR(l_to, 1, INSTR(l_to, ‘,’) - 1);
      l_to     := SUBSTR(l_to, INSTR(l_to, ‘,’) + 1);
      UTL_SMTP.rcpt(l_mail_conn, l_dest_tmp);
   END LOOP;
   UTL_SMTP.rcpt(l_mail_conn, l_to);
   UTL_SMTP.open_data(l_mail_conn);
   UTL_SMTP.write_data(l_mail_conn, ‘Date: ‘ || TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) || CHR(13)|| CHR(10));
   UTL_SMTP.write_data(l_mail_conn, ‘From: ‘ || l_from || CHR(13)|| CHR(10));
   UTL_SMTP.write_data(l_mail_conn, ‘Subject: ‘ || l_subject || CHR(13)|| CHR(10));
   UTL_SMTP.write_data(l_mail_conn, ‘To: ‘ || l_to || CHR(13)|| CHR(10));
   UTL_SMTP.write_data(l_mail_conn, ‘le corps du mail contenant de données’ || CHR(13)|| CHR(10));
   UTL_SMTP.write_data(l_mail_conn, ” || CHR(13)|| CHR(10));
   – on itere sur les resultat et rajoutons les valeurs dans le corp du mail
   FOR fields IN
   (
      SELECT
         FIELD_1
      FROM
         MA_TABLE
   )LOOP
      UTL_SMTP.write_data(l_mail_conn, fields.FIELD_1 || CHR(13)|| CHR(10));
    END LOOP;
    UTL_SMTP.close_data(l_mail_conn);
    UTL_SMTP.quit(l_mail_conn);
 END;
END ENVOI_MAIL;

CREATE OR REPLACE directory TMP AS ‘/home/db/user1′;

CREATE OR REPLACE PROCEDURE QUERY_TO_CSV AS
BEGIN
DECLARE
f_file_id UTL_FILE.FILE_TYPE;
v_file_location VARCHAR2(256) := ‘TMP’;
v_line VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.put_line(v_file_location);
f_file_id := UTL_FILE.FOPEN(v_file_location,‘export.csv’,‘w’);
UTL_FILE.PUT_LINE(f_file_id,‘FIELD_1,FIELD_2,FIELD_3,FIELD_4,FIELD_5′);
FOR fields IN
(
SELECT
   FIELD_1,
   FIELD_2,
   FIELD_3,
   FIELD_4,
   FIELD_5
FROM
   MA_TABLE
)LOOP
UTL_FILE.PUT_LINE(f_file_id,fields.FIELD_1||‘,’||fields.FIELD_2||‘,’||fields.FIELD_3||‘,’||fields.FIELD_4||‘,’||fields.FIELD_5);
UTL_FILE.FCLOSE (f_file_id);
UTL_FILE.FCLOSE_ALL;
END LOOP;
END;
END QUERY_TO_CSV;