Question

Command_SYS.Mail - Attach file

  • 21 December 2021
  • 7 replies
  • 661 views
G
Rank
Badge +3

I am trying to use Command_SYS.Mail to Attach file created on the Database Server.

I figured out how to attach a file from the Application Server, but my Package will run on the Database server and create a file on the database server.

Is there anyway to tell Command_SYS.Mail to Attach a file from the Database Server rather than the Application Server?

Version: IFS App 8.

Thanks for any help.

This topic has been closed for comments

7 replies

william.klotz
Rank
Userlevel 7
Badge +21

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

G
Rank
Badge +3

Thanks William.

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

dsj
Rank
Userlevel 7
Badge +20

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

DSJ - https://dsj23.me
G
Rank
Badge +3

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.

dsj
Rank
Userlevel 7
Badge +20

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=[file content]

 

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

 

Cheers!
Damith

DSJ - https://dsj23.me
G
Rank
Badge +3

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

G
Rank
Badge +3

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.

Terms & ConditionsCookie settings