PL/SQL procedure output stored in file and then email in Oracle 11g

Question

I have a procedure where I am checking whether there are new codes. If there are then insert them into a table. And also save the new data into a csv or txt file and email them to me.

I can't get the logic for how to redirect the new data to file or just even put the data as simple text in the email. Thanks

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;

score 1 · Accepted Answer · answered May 02 '14 at 22:00

To write to a file the UTL_FILE package will come in handy. To write an email you'll need to put the text to be sent into some sort of string before passing it to the MESSAGE argument of UTL_MAIL.SEND. You'll also need to be sure UTL_MAIL is installed and set up on your server; see this FAQ.

So something like the following may be useful:

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     => 'me@mycompany.com',
                recipients => 'you@someplaceelse.net',
                subject    => 'New codes',
                message    => strMessage);

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

Share and enjoy.

Thank you for your input. If I want to send the output written to file as an attachment to the mail being sent, do I need to use "utl_smtp" ? — Xasthur, May 03 '14 at 23:07
And also, if a file doesn't exist before a procedure is run. Can we create a file when proc is run ? I didn't find anywhere that we can create a file using UTL_FILE and then do write, instead of passing file name explicitly — Xasthur, May 03 '14 at 23:19
I haven't used UTL_SMTP so can't comment. As far as creating the file goes, when the third argument to UTL_FILE.FOPEN is 'w' it will create the file if it does not exist; if the file does exist its size will be truncated to zero. — Bob Jarvis - Слава Україні, May 04 '14 at 02:21

PL/SQL procedure output stored in file and then email in Oracle 11g

1 Answers1