Вывод процедуры PL/SQL сохраняется в файле, а затем отправляется по электронной почте в Oracle 11g

Question

У меня есть процедура, где я проверяю, есть ли новые коды. Если есть, вставьте их в таблицу. А также сохраните новые данные в CSV или TXT-файл и отправьте их мне по электронной почте.

Я не могу понять, как перенаправить новые данные в файл или просто поместить данные в виде простого текста в электронное письмо. Спасибо

create or replace
PROCEDURE new_codes_test( today_date IN VARCHAR2 DEFAULT NULL,  v_proc_return OUT NUMBER) AS
sql_str         VARCHAR2(4000);
.....

BEGIN
    v_start_time    := SYSDATE;
    v_proc_return   := 0;
   ....
      INSERT INTO NEW_CODES_test
      SELECT DISTINCT   Sy_ID ,P_CODE, SYSDATE
      FROM X.B
      WHERE NOT EXISTS (SELECT DISTINCT Sy_ID, P_CODE 
                        FROM X.C
                        WHERE today = today_date) ;

     COMMIT;

 --SELECT ___ into ___ from X.B;

sql_str := 'UTL_MAIL.send(sender       => ''
                         ,recipients   => ''
                         ,cc           => ''
                         ,subject      => 'New codes'
                         ,MESSAGE      => '' )';

 --EXECUTE IMMEDIATE sql_str;
 p_proc_return     :=  v_proc_return;

EXCEPTIONS
  ....
  END;

Answer 1

Для записи в файл пригодится пакет UTL_FILE. Чтобы написать электронное письмо, вам нужно поместить текст для отправки в какую-то строку, прежде чем передавать его в аргумент MESSAGE UTL_MAIL.SEND. Вы также должны быть уверены, что UTL_MAIL установлен и настроен на вашем сервере; см. этот FAQ

Поэтому может быть полезно что-то вроде следующего:

CREATE OR REPLACE FUNCTION NEW_CODES_TEST(today_date IN VARCHAR2)
  RETURN NUMBER
AS
  strMessage  VARCHAR2(32767);
  nRows       NUMBER := 0;
  fHandle     UTL_FILE.FILE_TYPE;
BEGIN
  v_start_time    := SYSDATE;

  fHandle := UTL_FILE.FOPEN(someDirectory, someFilename, 'w');

  FOR aRow IN (SELECT DISTINCT SY_ID ,P_CODE
                 FROM X.B
                 WHERE NOT EXISTS (SELECT DISTINCT Sy_ID, P_CODE 
                                     FROM X.C
                                     WHERE today = today_date)
  LOOP
    INSERT INTO NEW_CODES_test
      VALUES (aRow.SY_ID, aRow.P_CODE, SYSDATE);

    UTL_FILE.PUT_LINE(fHandle, aRow.SY_ID || ', ' || aRow.P_CODE);

    strMessage := strMessage || 'Added ' || aRow.SY_ID || ', ' ||
                                aRow.P_CODE || CHR(10);

    nRows := nRows + 1;
  END LOOP;

  COMMIT;

  UTL_FILE.FCLOSE(fHandle);

  UTL_MAIL.SEND(sender     => '[e-mail]',
                recipients => '[e-mail]',
                subject    => 'New codes',
                message    => strMessage);

  RETURN 0;
EXCEPTIONS
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RETURN SQLCODE;
END NEW_CODES_TEST;

Поделитесь и наслаждайтесь.