Hi @GNDKSCHULTZ ,

You don’t say where you’re getting the attachment from but if you’re getting it from document management you could something similar to the code below.  Just change it to meet your requirements.    I’ve used the code in an Event Action most recently but you could you modify it to work in a package and pass the relevant information to the package.

DECLARE
  mailto_ VARCHAR2(800) := 'email_to_user@domain.com';
  mailfrom_ VARCHAR2(800) := 'email_from_user@domain.com';
  file_name_ varchar2(2000);
  file_names_ varchar2(2000);
  doc_type_ varchar2(2000);
  AM Plsqlap_Record_API.type_record_;
  f_ varchar2(2000);

  doc_class_ VARCHAR2(100) := '&NEW:DOC_CLASS';
  doc_no_ VARCHAR2(100) := '&NEW:DOC_NO';
  doc_sheet_ VARCHAR2(100) := '&NEW:DOC_SHEET';
  doc_rev_ VARCHAR2(100) := '&NEW:DOC_REV';

BEGIN
  EDM_FILE_API.REFERENCE_EXIST(F_,doc_class_, doc_no_, doc_sheet_, doc_rev_,'VIEW');
  IF F_ = 'TRUE' THEN
     doc_type_ := 'VIEW';
  ELSE
     doc_type_ := 'ORIGINAL';
  END IF;

  file_name_ := EDM_FILE_API.GET_NEW_LOCAL_FILE_NAME(doc_class_, doc_no_, doc_sheet_, doc_rev_,doc_type_,'1');

  AM := 
     Plsqlap_Record_API.New_record('DOCUMENT_FILE_DATA');
     Plsqlap_Record_API.Set_Value (AM, 'DOC_CLASS', doc_class_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'DOC_NO',    doc_no_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'DOC_SHEET', doc_sheet_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'DOC_REV',   doc_rev_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'DOC_TYPE',  doc_type_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'FILE_NO', '1', Plsqlap_Record_API.dt_Integer, FALSE);
     Plsqlap_Record_API.Set_Value (AM, 'LOCAL_FILE_NAME',file_name_, Plsqlap_Record_API.dt_Text_Key, FALSE);
     Plsqlap_Server_API.Invoke_Record_Impersonate('BatchTransferHelper', 'readFromDatabase', AM);

     COMMAND_SYS.MAIL(
         FROM_USER_NAME_ => mailfrom_,
         TO_USER_NAME_ => mailto_,
         TEXT_ => 'Please find attached document',
         SUBJECT_ => 'document attached',
         ATTACH_ =>  file_name_ );
END;

Regards,

William Klotz

Thanks William.

However, I create my file using UTL_FILE which gets stored in an Oracle defined directory.

Hi @GNDKSCHULTZ 

We had a similar problem in one of our customers and we shared the oracle directory with application server and accessed the file using shared path.

Hope it helps!

Damith

Thanks Damith,

I had thought of that, but sometimes it is very slow to get things done. I submitted a request, but I never know how long it will take.

I figured that Command_Sys.Mail must have an option in to tell it to use a directory on the Database Server rather than the Application Server, which seemed reasonable since the Package runs on the Database.

In the meantime, I’ve written a Bash script to poll the database directory and send the email with the attachment.

If your file is a text file, then you might have a chance. Command_SYS.Mail attachment can be either a file path or content of the file. If you use UTL_FILE to read the file in the event and then attach, hopefully it could work.

use following format in the attachment

YOUR_ATTACHMENT_NAME.txt=tfile content]

Unfortunately Apps8 only supports text files so this is not much of a use.

Cheers!
Damith

Actually, Text is fine, however due to the size limitation (32,000 bytes), it doesn’t solve the problem.

Thanks for all the attempted help.

Unfortunately, I was unable to use the Command_SYS.Mail attachment option due to LAN Segment issues.

Ultimately, I wrote a BASH script to send the report as an HTML attachment using sendmail mail.