Home » SQL & PL/SQL » SQL & PL/SQL » Mail csv file as an attachment In Oracle
Mail csv file as an attachment In Oracle [message #398774] Fri, 17 April 2009 08:58 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi..all

i know how to send mail through oracle.But i want to send a file as an attachment (not the content,But as a file).
i.e.
It should attch a file which is under one specific directory

Thanks & regards
Sriram
Re: Mail csv file as an attachment In Oracle [message #398777 is a reply to message #398774] Fri, 17 April 2009 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should search for this FAQ.

Regards
Michel
Re: Mail csv file as an attachment In Oracle [message #398779 is a reply to message #398774] Fri, 17 April 2009 09:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First hit from google.
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL
Re: Mail csv file as an attachment In Oracle [message #398789 is a reply to message #398779] Fri, 17 April 2009 09:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

Thanks Mahesh Rajendran & Michel

i searched for that before posting on orafaq.

But as it is only for the type like again we need to fetch the content of the file,then it will save the content as an attachment

i.e.if we submit 'CSV,file,attachement' then a file excel.csv created with the above content.
so what i need to do is again i need to call 'UTL_%' package to get the content,
But as i am looking for direct method.Please let me know if any

Thanks & regards
Sriram.

Re: Mail csv file as an attachment In Oracle [message #398793 is a reply to message #398774] Fri, 17 April 2009 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
If you use the SEARCH capability of this forum while inputting "mail" & "attachment" you get 150 Search Results Found.


Your question is a FAQ & is NOT really an Oracle problem.
Creating an attachment is an SMTP problem.

Good luck with reinventing the wheel.
Re: Mail csv file as an attachment In Oracle [message #398797 is a reply to message #398793] Fri, 17 April 2009 11:29 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
there are several methods for reading a flat file into pl/sql (or table) including utl_file or external table. Obviously the file needs to be accessible from the Oracle user on the Oracle server.

   --create or replace directory MY_DIR as '/users/mydir';
   --CREATE TABLE EXT_TAB
   --(LINE     VARCHAR2(4000))
   --ORGANIZATION EXTERNAL
   --  (  TYPE ORACLE_LOADER
   --     DEFAULT DIRECTORY MY_DIR
   --     ACCESS PARAMETERS
   --       ( RECORDS DELIMITED BY NEWLINE
   --      NOBADFILE
   --      NODISCARDFILE
   --      NOLOGFILE
   --      DATE_CACHE 0
   --      FIELDS
   --         MISSING FIELD VALUES ARE NULL
   --         REJECT ROWS WITH ALL NULL FIELDS
   --         (LINE          CHAR (4000))
   --                         )
   --     LOCATION ('my_file.txt')
   --  )
   --REJECT LIMIT UNLIMITED;
   PROCEDURE fload (p_file IN VARCHAR2)
   AS
      v_ddl   VARCHAR2 (200);
   BEGIN
      v_ddl := 'alter table EXT_TAB location(' || '''' || p_file || ''')';
 
      EXECUTE IMMEDIATE v_ddl;
 
      for i in (SELECT ROWNUM rn, line  FROM EXT_TAB) loop
         -- do something 
         null;
      end loop;
   END;
 


Another easy (maybe clunky) way is to just create a script to send the mail and call that script from a scheduler job (or Java stored proc). There are plenty of examples of DBMS_SCHEDULER.create_job (job_type 'EXECUTABLE') or the messier Java approach.

http://www.orafaq.com/forum/t/80573/45693/



Previous Topic: Select query have issue.?
Next Topic: COM Object or Command Line Triggers
Goto Forum:
  


Current Time: Sat Dec 03 08:10:57 CST 2016

Total time taken to generate the page: 0.07642 seconds