Email attachment through a stored procedure in Oracle

Okay --  with the JAVA stuff said,

I read a document about using the SMTP package at:
http://home.clara.net/dwotton/dba/oracle_smtp.txt 

It looks good, but again I have not tried it.

Another package you can try is PSP_MAIL at:
http://www.dpsp-yes.com/dpsp/prod/!go?id_=nproduct
PSP_Mail is the Dynamic PSP add-on which can be also used standalone, encapsulating SMTP interface provided in Oracle8i with our DNS lookup functions in a single easy-to-use package that gives Oracle PL/SQL developers a simple way to send e-mail from Oracle RDBMS without the need for any external programs/scripts


Another option:   You can encode  MIME formating with UTL_SMTP
It is possible -- I have used this with different charactersets,  but not attachements.

I hope these suggestions help.
sincereley,
dBalaski

I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB

This is the stored procedure. Only modify and call it!

You need version >=8.1.6, and Java installed for this to work.
Create a job that checks what it needs to check and when needed, runs this:

PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);

-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);

BEGIN

vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);

-- Splits body (long) into varchar2s.

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP

DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);

END LOOP;

UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);


EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;

BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20100, vSqlErrm);

WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;

I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB

I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB

I sense from your three posts about your environment, that you're looking for something different.  To accomplish this without using Java, you can use the UTL_SMTP PL/SQL supplied procedure.  To find out more, look here:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/utl_smtp.htm#1000695

(registration is required, but free)

There you will find this example, whi I believe does what you want, or could be called by a procedure that does whatever else you wish to do:

The following code example illustrates how the SMTP package might be used by an application to send email. The application connects to an SMTP server at port 25 and sends a simple text message.

PROCEDURE send_mail (sender    IN VARCHAR2,
                     recipient IN VARCHAR2,
                     message   IN VARCHAR2)
IS
    mailhost    VARCHAR2(30) := 'mailhost.mydomain.com';
    mail_conn  utl_smtp.connection;

BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);
    utl_smtp.data(mail_conn, message);
    utl_smtp.quit(mail_conn);
EXCEPTION
    WHEN OTHERS THEN
        -- Handle the error
END;

Good luck!

Mail itself is easy with UTL_SMTP,  

sending attachments with  mail is not as easy...

That I why I presented those options --   The Java Stored Procedure seems the most promising option.  
Remember -- this is JAVA STORED IN THE DATABASE utilizing the Oracle JServer running in the Database.
So it won't matter what the client is running -  vb,  Access,  whatever...

The other options seem promising --   although one is a bit costly  (the PSP_MAIL).

dBalaski

Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

Mindphaser,
    Unless gsbharathi comes back and decides I'd suggest a split between dbalaski and schwertner.  On rereading, I find my answer to be somewhat repetitive, and without the attachments.

Force accepted

** Mindphaser - Community Support Moderator **

schwertner, there will be a separate question with points for your help.