1 2 Previous Next 19 Replies Latest reply: Jan 26, 2011 3:44 PM by R RSS

    Not able to send an output using utl_smtp

    R
      Hi All,
      I am trying to use utl_smtp to send the pdf output(>32KB) to an email.
      The below procedure is running successfully but not getting any email. Can any one figure out where exactly i am going wrong.

      create or replace PROCEDURE send_mail123 (
      errbuf VARCHAR2,
      retode NUMBER,
      p_sender IN VARCHAR2,
      p_recipient IN VARCHAR2,
      p_message IN VARCHAR2)

      /*,
      filename1 IN VARCHAR2 DEFAULT NULL,
      filetype1 IN VARCHAR2 DEFAULT 'text/plain')*/
      as
      l_mailhost VARCHAR2(255);
      smtp_port NUMBER := 25;
      l_mail_conn utl_smtp.connection;
      boundary CONSTANT VARCHAR2 (256) := 'CES.Boundary.DACA587499938898';
      directory_path VARCHAR2 (256);
      file_name VARCHAR2 (256);
      crlf VARCHAR2 (2) := CHR (13) || CHR (10);
      mesg VARCHAR2 (32767);

      filename1 VARCHAR2(1000) := '/u07/applmgr/dev/devcomn/admin/out/DEV_wg-oa-01/RGADWALA.5928476';
      filetype1 VARCHAR2(1000) := 'application/pdf';


      l_length NUMBER;
      l_sub VARCHAR2 (32767);

      TYPE varchar2_table IS TABLE OF VARCHAR2 (256)
      INDEX BY BINARY_INTEGER;

      file_array varchar2_table;
      type_array varchar2_table;
      i BINARY_INTEGER;

      -- Procedure to split a file pathname into its directory path and file name
      -- components.
      PROCEDURE split_path_name (
      file_path IN VARCHAR2,
      directory_path OUT VARCHAR2,
      file_name OUT VARCHAR2
      )
      IS
      pos NUMBER;
      BEGIN
      fnd_file.put_line (fnd_file.log, 'Inside the SPLIT PATH NAME Begin');
      -- Separate the filename from the directory name
      pos := INSTR (file_path, '/', -1);

      IF pos = 0
      THEN
      pos := INSTR (file_path, '\', -1);
      END IF;

      fnd_file.put_line (fnd_file.log, 'vALUE FOR pos' || POS);
      IF pos = 0
      THEN
      directory_path := NULL;
      ELSE
      directory_path := SUBSTR (file_path, 1, pos - 1);
      END IF;

      file_name := SUBSTR (file_path, pos + 1);
      fnd_file.put_line (fnd_file.log, 'vALUE FOR FILE NAME' ||FILE_NAME);
      END;

      -- Procedure to append a file's contents to the e-mail
      PROCEDURE append_file (
      directory_path IN VARCHAR2,
      file_name IN VARCHAR2,
      file_type IN VARCHAR2,
      l_mail_conn IN OUT UTL_SMTP.connection
      )
      IS
      generated_name VARCHAR2 (30) := 'CESDIR' || TO_CHAR (SYSDATE, 'HH24MISS');
      directory_name VARCHAR2 (30);
      file_handle UTL_FILE.file_type;
      bfile_handle BFILE;
      bfile_len NUMBER;
      pos NUMBER;
      read_bytes NUMBER;
      line VARCHAR2 (1000);
      DATA RAW (200);
      my_code NUMBER;
      my_errm VARCHAR2 (32767);
      BEGIN
      BEGIN
      -- Grant access to the directory, unless already defined, and open
      -- the file (as a bfile for a binary file, otherwise as a text file).
      BEGIN
      fnd_file.put_line (fnd_file.log, 'Inside the APPEND FILE Begin');
      line := directory_path;

      fnd_file.put_line (fnd_file.log, 'VALUE FOR LINE' ||LINE );
      SELECT dd.directory_name
      INTO directory_name
      FROM all_directories dd
      WHERE dd.directory_path = line
      AND ROWNUM = 1;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      directory_name := generated_name;
      END;
      fnd_file.put_line (fnd_file.log, 'VALUE FOR DIRECTORY' ||directory_name );
      fnd_file.put_line (fnd_file.log, 'VALUE FOR FILE TYPE' ||file_type );
      IF SUBSTR (file_type, 1, 4) != 'text'
      THEN
      fnd_file.put_line (fnd_file.log, 'Inside the IF condition' );
      bfile_handle := BFILENAME (directory_name, file_name);
      bfile_len := DBMS_LOB.getlength (bfile_handle);
      pos := 1;
      DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly);
      ELSE
      file_handle := UTL_FILE.fopen (directory_name, file_name, 'r');
      END IF;

      -- Append the file contents to the end of the message
      LOOP
      -- If it is a binary file, process it 57 bytes at a time,
      -- reading them in with a LOB read, encoding them in BASE64,
      -- and writing out the encoded binary string as raw data
      IF SUBSTR (file_type, 1, 4) != 'text'
      THEN
      IF pos + 57 - 1 > bfile_len
      THEN
      fnd_file.put_line (fnd_file.log, 'Inside the 2 IF condition' );
      read_bytes := bfile_len - pos + 1;
      ELSE
      read_bytes := 57;
      END IF;

      --fnd_file.put_line (fnd_file.log, 'Before the Read' );

      DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA);
      UTL_SMTP.write_raw_data (l_mail_conn, UTL_ENCODE.base64_encode (DATA));
      pos := pos + 57;

      IF pos > bfile_len
      THEN
      fnd_file.put_line (fnd_file.log, 'If pos > bfile then Exit' );
      EXIT;
      END IF;
      -- If it is a text file, get the next line of text, append a
      -- carriage return / line feed to it, and write it out
      ELSE
      fnd_file.put_line (fnd_file.log, 'In the ELSE' );
      UTL_FILE.get_line (file_handle, line);
      UTL_SMTP.write_data (l_mail_conn, line || crlf);
      END IF;
      END LOOP;
      -- Output any errors, except at end when no more data is found
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      NULL;
      WHEN OTHERS
      THEN
      my_code := SQLCODE;
      my_errm := SQLERRM;
      DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm);
      END;

      -- Close the file (binary or text)
      IF SUBSTR (file_type, 1, 4) != 'text'
      THEN
      DBMS_LOB.CLOSE (bfile_handle);
      fnd_file.put_line (fnd_file.log, 'Close Bfile' );
      ELSE
      UTL_FILE.fclose (file_handle);
      END IF;


      END;

      BEGIN
      SELECT value value1
      INTO l_mailhost
      FROM v$parameter
      WHERE name like '%smtp%';

      fnd_file.put_line (fnd_file.log, 'Inside the Begin');

      file_array (1) := filename1;
      type_array (1) := filetype1;

      fnd_file.put_line (fnd_file.log, 'Before Opening the connection');

      l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
      utl_smtp.helo(l_mail_conn, l_mailhost);
      utl_smtp.mail(l_mail_conn, p_sender);
      utl_smtp.rcpt(l_mail_conn, p_recipient);

      fnd_file.put_line (fnd_file.log, 'Before opening the data connection');

      utl_smtp.open_data(l_mail_conn );
      -- Build the start of the mail message
      mesg :=
      'Date: '
      || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
      || crlf
      || 'From: '
      || P_sender
      || crlf
      || 'Subject: '
      || 'New Invoices'
      || crlf
      || 'To: '
      || p_recipient
      || crlf;

      mesg :=
      mesg
      || 'Mime-Version: 1.0'
      || crlf
      || 'Content-Type: multipart/mixed; boundary="'
      || boundary
      || '"'
      || crlf
      || crlf
      || 'This is a Mime message, which your current mail reader may not'
      || crlf
      || 'understand. Parts of the message will appear as text. If the remainder'
      || crlf
      || 'appears as random characters in the message body, instead of as'
      || crlf
      || 'attachments, then you''ll have to extract these parts and decode them'
      || crlf
      || 'manually.'
      || crlf
      || crlf;
      utl_smtp.write_data(l_mail_conn, p_message);
      fnd_file.put_line (fnd_file.log, 'After Opening the Data Connection'||mesg);
      fnd_file.put_line (fnd_file.log, 'After Utl Write');

      fnd_file.put_line (fnd_file.log, 'P_MESSAGE'|| P_MESSAGE);

      IF P_MESSAGE IS NOT NULL
      THEN
      mesg :=
      '--'
      || boundary
      || crlf
      || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII'
      || crlf
      || 'Content-Disposition: inline; filename="message.txt"'
      || crlf
      || 'Content-Transfer-Encoding: 7bit'
      || crlf
      || crlf;
      UTL_SMTP.write_data (l_mail_conn, mesg);

      IF SUBSTR (P_MESSAGE, 1, 1) = '/'
      THEN
      split_path_name (P_MESSAGE, directory_path, file_name);
      append_file (directory_path, file_name, 'text', l_mail_conn);
      UTL_SMTP.write_data (l_mail_conn, crlf);
      ELSE
      UTL_SMTP.write_data (l_mail_conn, P_MESSAGE || crlf);
      END IF;
      END IF;


      fnd_file.put_line (fnd_file.log, 'Before the For Loop');
      FOR i IN 1 .. 3
      LOOP
      fnd_file.put_line (fnd_file.log, 'Inside the For Loop');
      -- If the filename has been supplied ...
      IF file_array (i) IS NOT NULL
      THEN
      fnd_file.put_line (fnd_file.log, 'Inside file array1 '||file_name);
      split_path_name (file_array (i), directory_path, file_name);
      -- Generate the MIME boundary line according to the file (mime) type
      -- specified.
      mesg := crlf || '--' || boundary || crlf;

      SELECT INSTR (file_name, '.')
      INTO l_length
      FROM DUAL;

      fnd_file.put_line (fnd_file.log, 'Length '||l_length);

      /*SELECT SUBSTR (file_name, 1, l_length - 1) || '.pdf'
      INTO l_sub
      FROM DUAL;*/

      SELECT (file_name) || '.pdf'
      INTO l_sub
      FROM DUAL;

      fnd_file.put_line (fnd_file.log, 'l_sub '||l_sub);

      IF SUBSTR (type_array (i), 1, 4) != 'text'
      THEN
      fnd_file.put_line (fnd_file.log, 'lnside the array');
      mesg :=
      mesg
      || 'Content-Type: '
      || type_array (i)
      || '; name="'
      || file_name
      || '"'
      || crlf
      || 'Content-Disposition: attachment; filename="'
      || file_name
      || '"'
      || crlf
      || 'Content-Transfer-Encoding: base64'
      || crlf
      || crlf;
      ELSE
      mesg :=
      mesg
      || 'Content-Type: application/octet-stream; name="'
      || file_name
      || '"'
      || crlf
      || 'Content-Disposition: attachment; filename="'
      || file_name
      || '"'
      || crlf
      || 'Content-Transfer-Encoding: 7bit'
      || crlf
      || crlf;
      END IF;
      fnd_file.put_line (fnd_file.log, 'mesg '||mesg);
      UTL_SMTP.write_data (l_mail_conn, mesg);
      -- Append the file contents to the end of the message
      append_file (directory_path, file_name, type_array (i), l_mail_conn);
      fnd_file.put_line (fnd_file.log, 'After apending the data ');
      UTL_SMTP.write_data (l_mail_conn, crlf);
      END IF;
      END LOOP;

      fnd_file.put_line (fnd_file.log, 'End Loop');

      mesg := crlf || '--' || boundary || '--' || crlf;
      UTL_SMTP.write_data (l_mail_conn, mesg);
      -- Close the SMTP connection
      utl_smtp.close_data(l_mail_conn );
      utl_smtp.quit(l_mail_conn);
      end;

      ------------------------------------------------------------
      --Log File Generated for the above program
      ------------------------------------------------------------
      ---------------------------------------------------------------------------
      Start of log messages from FND_FILE
      ---------------------------------------------------------------------------
      Inside the Begin
      Before Opening the connection
      Before opening the data connection
      After Opening the Data ConnectionDate: 19 Jan 11 14:35:38
      From: r.gadwala@hov.com
      Subject: New Invoices
      To: r.gadwala@hov.com
      Mime-Version: 1.0
      Content-Type: multipart/mixed; boundary="CES.Boundary.DACA587499938898"

      This is a Mime message, which your current mail reader may not
      understand. Parts of the message will appear as text. If the remainder
      appears as random characters in the message body, instead of as
      attachments, then you'll have to extract these parts and decode them
      manually.


      After Utl Write
      P_MESSAGETesting
      Before the For Loop
      Inside the For Loop
      Inside file array1
      Inside the SPLIT PATH NAME Begin
      vALUE FOR pos48
      vALUE FOR FILE NAMERGADWALA.5928476
      Length 9
      l_sub RGADWALA.5928476.pdf
      lnside the array
      mesg
      --CES.Boundary.DACA587499938898
      Content-Type: application/pdf; name="RGADWALA.5928476"
      Content-Disposition: attachment; filename="RGADWALA.5928476"
      Content-Transfer-Encoding: base64


      Inside the APPEND FILE Begin
      VALUE FOR LINE/u07/applmgr/dev/devcomn/admin/out/DEV_wg-oa-01
      VALUE FOR DIRECTORYOUT_DIR
      VALUE FOR FILE TYPEapplication/pdf
      Inside the IF condition
      Inside the 2 IF condition
      If pos > bfile then Exit
      Close Bfile
      After apending the data
      Inside the For Loop
      ---------------------------------------------------------------------------
      End of log messages from FND_FILE
      ---------------------------------------------------------------------------

      Regards,
      RR
        1 2 Previous Next