How to send email using Oracle 10 g Forms

Hmusman

i make a form i want when i click on button it send email i get a code for email from internet

    CREATE OR REPLACE FUNCTION FSC.SEND_MAIL

    (pIssuer IN VARCHAR2,

    pReceiver IN VARCHAR2,

    pSender IN VARCHAR2,

    pSubject IN VARCHAR2,

    pMessage IN VARCHAR2) RETURN VARCHAR2 IS

   

    c utl_smtp.connection;

    respuesta utl_smtp.reply;

    pServer VARCHAR2(50) := '192.168.0.6';

   

    BEGIN

   

    -- Open the connection to the mail server

    c := utl_smtp.open_connection(pServer);

    respuesta := utl_smtp.helo(c, pServer);

   

    -- Start the Issuer mail.

    respuesta := utl_smtp.mail(c, pSender);

   

    -- Starts Receiver

    respuesta := utl_smtp.rcpt(c, pReceiver);

   

    respuesta := utl_smtp.open_data(c);

    -- Enter the email header

    utl_smtp.write_data(c, 'From: ' || pIssuer || utl_tcp.CRLF);

    utl_smtp.write_data(c, 'To: ' || pReceiver || utl_tcp.CRLF);

    -- Enter the Subject

    utl_smtp.write_data(c, 'Subject: ' || pSubject || utl_tcp.CRLF);

    -- Write the message text.

    utl_smtp.write_data(c, utl_tcp.CRLF || pMessage);

    utl_smtp.write_data(c, utl_tcp.CRLF || '.');

   

    respuesta := utl_smtp.close_data(c);

   

    -- Close connection

    respuesta := utl_smtp.quit(c);

   

    RETURN '0';

   

    EXCEPTION

    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN

    utl_smtp.quit(c);

    RETURN sqlerrm;

    --raise_application_error(-20000,

    -- 'The sending of the email has failed by returning the following error: ' || sqlerrm);

    WHEN OTHERS THEN

    RETURN sqlerrm;

    END;

    /

i make this function in sql it  and it was successfully run but when i execute then no email send to my desire address

    declare

   

    begin

   

    dbms_output.put_line(SEND_MAIL('[email protected]','[email protected]','[email protected]','Testing','email message'));

    end;

i make a form i want when i click on button it send email i get a code for email from internet

        CREATE OR REPLACE FUNCTION FSC.SEND_MAIL

    (pIssuer IN VARCHAR2,

    pReceiver IN VARCHAR2,

    pSender IN VARCHAR2,

    pSubject IN VARCHAR2,

    pMessage IN VARCHAR2) RETURN VARCHAR2 IS

   

    c utl_smtp.connection;

    respuesta utl_smtp.reply;

    pServer VARCHAR2(50) := '192.168.0.6';

   

    BEGIN

   

    -- Open the connection to the mail server

    c := utl_smtp.open_connection(pServer);

    respuesta := utl_smtp.helo(c, pServer);

   

    -- Start the Issuer mail.

    respuesta := utl_smtp.mail(c, pSender);

   

    -- Starts Receiver

    respuesta := utl_smtp.rcpt(c, pReceiver);

   

    respuesta := utl_smtp.open_data(c);

    -- Enter the email header

    utl_smtp.write_data(c, 'From: ' || pIssuer || utl_tcp.CRLF);

    utl_smtp.write_data(c, 'To: ' || pReceiver || utl_tcp.CRLF);

    -- Enter the Subject

    utl_smtp.write_data(c, 'Subject: ' || pSubject || utl_tcp.CRLF);

    -- Write the message text.

    utl_smtp.write_data(c, utl_tcp.CRLF || pMessage);

    utl_smtp.write_data(c, utl_tcp.CRLF || '.');

   

    respuesta := utl_smtp.close_data(c);

   

    -- Close connection

    respuesta := utl_smtp.quit(c);

   

    RETURN '0';

   

    EXCEPTION

    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN

    utl_smtp.quit(c);

    RETURN sqlerrm;

    --raise_application_error(-20000,

    -- 'The sending of the email has failed by returning the following error: ' || sqlerrm);

    WHEN OTHERS THEN

    RETURN sqlerrm;

    END;

    /

i make this function in sql it  and it was successfully run but when i execute then no email send to my desire address

    declare

   

    begin

   

    dbms_output.put_line(SEND_MAIL('[email protected]','[email protected]','[email protected]','Testing','email message'));

    end;

i use my local exchange for eamil sending  and the ip address of that server is 192.168.0.6

the second issue in this code when i make same function in Oracle 10 g forms then it give me this error

utl_tcp.CRLF is can not directly acces remote package

Kalpataru

Try this

crlf varchar2(2) := chr(10)||chr(13);

it will work.

HamidHelal

What is your database version ?

Did you give privilege SYS UTL_ENCODE,UTL_SMTP and UTL_TCP to the user ? Are you assign the Mail serve port ?

Hamid

Hmusman

i user oracle 10 g and tell me way how to assign privileges to user

Kalpataru

Hi Hmusman,

Which privilege you are talking about.

If you want to give privilege to user connect to sys as sysdba give grant.

grant execute on FSC.SEND_MAIL to scott; <--scott is the username i have taken scott. You change according to your's username.

grant execute on UTL_ENCODE to scott;

grant execute on UTL_SMTP to scott;

grant execute on UTL_TCP to scott;

Hmusman

i use oracle express edition so i cant i find

.SEND_MAIL , UTL_ENCODE, UTL_SMTP, UTL_TCP

HamidHelal

Hmusman wrote:

i use oracle express edition so i cant i find
.SEND_MAIL , UTL_ENCODE, UTL_SMTP, UTL_TCP 

This are the content of SYS. I don't remember Oracle XE has this.

Just connect SYS as SYSDBA and

issue

grant execute on UTL_ENCODE to <user_name>;

grant execute on UTL_SMTP to <user_name>;

grant execute on UTL_TCP to <user_name>;

Hope this helps

Hamid