LINUX SERVER MAIL CLIENT SHELL

create or replace procedure SP_SENDMAIL(pPROC varchar2, pTITLE varchar2, pTEXT varchar2, pATTACH varchar2, pHTML varchar2) AS
       v_COMMAND varchar2(8000);
       v_FROM varchar2(256);
       v_TO varchar2(256);
       v_CC varchar2(256);
       v_BCC varchar2(256);
       v_CALL number;
       v_DS_ERR varchar2(8000);
begin

  -- TODO: Questa procedura va modificata drasticamente, utilizzando il package UTL_MAIL [è più recente e nuovo rispetto a UTL_SMTP]

  v_COMMAND := NULL;
  v_FROM := NULL;
  v_TO := NULL;
  v_CC := NULL;
  v_BCC := NULL;

  IF instr(pPROC, '@') > 0 THEN
    v_TO := pPROC;
    v_FROM := 'no-reply@iper.it';
  ELSE
    DECLARE CURSOR email IS
      SELECT * FROM log_sendmail WHERE DS_PROC = pPROC AND FL_SEND = 'S';

    BEGIN
      FOR MSG IN email LOOP
        BEGIN
          v_FROM := MSG.DS_FROM;
          IF MSG.FL_TIPOINVIO = 'TO' THEN
             v_TO := v_TO || MSG.DS_TO || ' ';
          END IF;
          IF MSG.FL_TIPOINVIO = 'CC' THEN
             v_CC := v_CC || MSG.DS_TO || ' ';
          END IF;
          IF MSG.FL_TIPOINVIO = 'BCC' THEN
             v_BCC := v_BCC || MSG.DS_TO || ' ';
          END IF;

          UPDATE log_sendmail SET DT_LASTINVIO = SYSDATE
          WHERE DS_PROC = pPROC AND DS_TO = MSG.DS_TO;
        END;
      END LOOP;
    END;
    IF v_TO IS NULL THEN
      v_TO := 'progetto.sigo@iper.it';
      v_FROM := 'no-reply@iper.it';
    END IF;
  END IF;

  v_COMMAND := v_COMMAND || '/home/sigo/bin/sendEmail ';
  v_COMMAND := v_COMMAND || '-f ' || v_FROM || ' ';
  v_COMMAND := v_COMMAND || '-t ' || v_TO || ' ';
  IF v_CC IS NOT NULL THEN
    v_COMMAND := v_COMMAND || '-cc ' || v_CC;
  END IF;
  IF v_BCC IS NOT NULL THEN
    v_COMMAND := v_COMMAND || '-bcc ' || v_BCC;
  END IF;
  -- Sostituisco il trattino con l'underscore perché crea problemi al sendEmail.pl
  v_COMMAND := v_COMMAND || '-m ' || replace(pTEXT, '-', '_') || ' ';
  v_COMMAND := v_COMMAND || '-u ' || replace(pTITLE, '-', '_') || ' ';
  v_COMMAND := v_COMMAND || '-s exchange.iper.it ';
  IF pATTACH IS NOT NULL THEN
    v_COMMAND := v_COMMAND || '-a /home/sigo/tmp/' || pATTACH;
  END IF;

  SELECT FU_CALLOSPOOL('/bin/ksh ' || v_COMMAND) INTO v_CALL FROM DUAL;
  COMMIT;

  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    v_DS_ERR := SQLERRM;
    INSERT INTO LOG_ERROR (nr_id, ds_proc, cd_deposito, dt_ins, ds_msg, ds_error, cd_operatore, fl_email, ds_tipo)
    VALUES (FU_NEXT_VAL_COUNTER('LOG_ERROR'), 'SP_SENDMAIL', 0, SYSDATE, pPROC || ' ' || pTITLE, v_DS_ERR, 'system', 'N', 'EMAIL');
    COMMIT;

end;

UTL_MAIL

The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.

create or replace procedure SP_SENDMAIL(pPROC varchar2, pTITLE varchar2, pTEXT varchar2, pATTACH varchar2) AS
       v_FROM varchar2(256);
       v_TO varchar2(256);
       v_CC varchar2(256);
       v_BCC varchar2(256);
       v_DS_ERR varchar2(8000);
       
       v_utl_filehandler utl_file.file_type;
       v_rawfile RAW(32767);
       v_size NUMBER;
       v_block NUMBER;
       v_boolean BOOLEAN;
       v_file_dir varchar2(100);

begin

  v_FROM := NULL;
  v_TO := NULL;
  v_CC := NULL;
  v_BCC := NULL;

  IF instr(pPROC, '@') > 0 THEN
    v_TO := pPROC || ',';
    v_FROM := 'no-reply@iper.it';
  ELSE
    DECLARE CURSOR email IS
      SELECT * FROM log_sendmail WHERE DS_PROC = pPROC AND FL_SEND = 'S';

    BEGIN
      FOR MSG IN email LOOP
        BEGIN
          v_FROM := MSG.DS_FROM;
          IF MSG.FL_TIPOINVIO = 'TO' THEN
             v_TO := v_TO || MSG.DS_TO || ',';
          END IF;
          IF MSG.FL_TIPOINVIO = 'CC' THEN
             v_CC := v_CC || MSG.DS_TO || ',';
          END IF;
          IF MSG.FL_TIPOINVIO = 'BCC' THEN
             v_BCC := v_BCC || MSG.DS_TO || ',';
          END IF;

          UPDATE log_sendmail SET DT_LASTINVIO = SYSDATE
          WHERE DS_PROC = pPROC AND DS_TO = MSG.DS_TO;
        END;
      END LOOP;
    END;
    IF v_TO IS NULL THEN
      v_TO := 'progetto.sigo@iper.it,';
      v_FROM := 'no-reply@iper.it';
    END IF;
  END IF;
  
  v_TO := substr(v_TO, 1, length(v_TO) - 1);
  
  IF v_CC IS NOT NULL THEN
    v_CC := substr(v_CC, 1, length(v_CC) - 1);
  END IF;
  IF v_BCC IS NOT NULL THEN
    v_BCC := substr(v_BCC, 1, length(v_BCC) - 1);
  END IF;

  IF pATTACH IS NULL THEN
    utl_mail.send(sender => v_FROM,
              recipients => v_TO,
                      cc => v_CC,
                     bcc => v_BCC,
                 subject => pTITLE,
                 message => replace(pTEXT, '\n', chr(10)),
               mime_type => 'text; charset=us-ascii');
  ELSE
    v_file_dir := 'SIGO_TMP';
	  v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, pATTACH, 'r');
	  utl_file.fgetattr(v_file_dir, pATTACH, v_boolean, v_size, v_block);
	  utl_file.get_raw(v_utl_filehandler, v_rawfile, v_size);
	  utl_file.fclose(v_utl_filehandler);

    utl_mail.send_attach_raw(sender => v_FROM,
                         recipients => v_TO,
                                 cc => v_CC,
                                bcc => v_BCC,
                            subject => pTITLE,
                            message => replace(pTEXT, '\n', chr(10)),
                          mime_type => 'text; charset=us-ascii',
                         attachment => v_rawfile,
                         att_inline => FALSE,
                       att_filename => pATTACH);
  END IF;

  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    v_DS_ERR := SQLERRM;
    INSERT INTO LOG_ERROR (nr_id, ds_proc, cd_deposito, dt_ins, ds_msg, ds_error, cd_operatore, fl_email, ds_tipo)
    VALUES (FU_NEXT_VAL_COUNTER('LOG_ERROR'), 'SP_SENDMAIL', 0, SYSDATE, pPROC || ' ' || pTITLE, v_DS_ERR, 'system', 'N', 'EMAIL');
    COMMIT;

end;