This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 26, 2008 6:07 PM by 143894 RSS

SMTP Password required

437968 Newbie
Currently Being Moderated
My SMTP Server requires us to user a username/password. I noticed in the HTML DB Admin settings their is only options for server and port. How do I register a username/password?
  • 1. Re: SMTP Password required
    jkallman Employee ACE
    Currently Being Moderated
    Martin,

    Unfortunately, there is no way to accomplish this in HTML DB. We can consider this for a future release in HTML DB, but we haven't seen overwhelming demand for it.

    About all I can suggest is your own e-mail implementation in PL/SQL (using UTL_SMTP or the new-in-10g UTL_MAIL, although it looks like even UTL_MAIL does not let you provide credentials).

    It's possible but I'm not sure if putting an authenticated relay server between HTML DB and the outbound SMTP server would circumvent this.

    Joel
  • 2. Re: SMTP Password required
    dccase Explorer
    Currently Being Moderated
    You can use UTL_SMTP to send mail through a server that requires authentication by sending the authentication commands after connecting. I use a procedure like the one below. I submit it using DBMS_JOB.

    CREATE OR REPLACE PROCEDURE "SEND_MAIL" (
    p_sender IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_message IN VARCHAR2
    )
    AS
    l_mailhost VARCHAR2 (255) := 'your.outgoing.mailserver.address';
    l_mail_conn UTL_SMTP.connection;
    BEGIN
    l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
    utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail UserName' ))) );
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail Password' ))) );
    UTL_SMTP.helo (l_mail_conn, l_mailhost);
    UTL_SMTP.mail (l_mail_conn, p_sender);
    UTL_SMTP.rcpt (l_mail_conn, p_recipient);
    UTL_SMTP.open_data (l_mail_conn);
    utl_smtp.write_data(l_mail_conn, 'Subject: Your Subject Line Here');
    UTL_SMTP.write_data (l_mail_conn, p_message);
    UTL_SMTP.close_data (l_mail_conn);
    UTL_SMTP.quit (l_mail_conn);
    END;
  • 3. Re: SMTP Password required
    selezeus Newbie
    Currently Being Moderated
    Im trying to use properly the code that you describe here to send emails with authentificate username and password.

    But it dont work.

    Maybe Can you describe it exactly it or send a working copy of it?

    Thanks in advanced.
    Regards.
  • 4. Re: SMTP Password required
    509819 Newbie
    Currently Being Moderated
    Hi - I'm trying to do this too, but the task of figuring out ow to use UTL_SMTP is daunting :( Would be nice to have this built into APEX :^) fyi - Comcast is my ISP, and they require uname/pwd.
  • 5. Re: SMTP Password required
    485735 Newbie
    Currently Being Moderated
    I have posted on Sourceforge an implementation of utl_smtp that can handle smtp authentication and few other bells and whistles. The project is called Clever Ideas For Oracle (http://sourceforge.net/projects/plcodebrew).

    Enjoy,
    Michael O'Neill
  • 6. Re: SMTP Password required
    489360 Newbie
    Currently Being Moderated
    I have need to do some changes in ORACLE 9:

    l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
    utl_smtp.EHLO(l_mail_conn, l_mailhost);
    utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail UserName'))) );
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail Password' ))) );
    utl_smtp.mail (l_mail_conn, p_sender);
    ...

    by
  • 7. Re: SMTP Password required
    Jes Oracle ACE
    Currently Being Moderated
    Hi,

    I'm not clear on whether you're asking a question here or whether you're saying you needed to change that code to get it to work on 9i?
  • 8. Re: SMTP Password required
    608513 Newbie
    Currently Being Moderated
    I've the same problem.

    My ISP requires POP-operation (check incoming emails) for authentification before sending.

    AFAIK, the UTL_POP package is absent and I can't modify ApEx API for writing own sendmail procedure with UTL_TCP/UTL_SMTP. But guys, it needs! How I can reset ApEx users passwords without e-mail notifications? And not in future-future releases, but now!

    AFAIK, all SMTP has blocked relays due to security reasons. And now, we have nothing? Don't use SMTP? You think, it good idea?
  • 9. Re: SMTP Password required
    Tyler Expert
    Currently Being Moderated
    First, statements like "And not in future-future releases, but now!" are likely to cause people to ignore your thread, as it sounds very demanding.

    One option you could try is to install your own SMTP server and have it relay messages through your ISP's SMTP server. You should be able to configure it to connect to your ISP's SMTP server with a username and password. You'll obviously not want it to require a username and password from the database. Keep in mind I have not done this, but it's one possible solution to research.

    Tyler
  • 10. Re: SMTP Password required
    625385 Newbie
    Currently Being Moderated
    I think its important to point out this code does it! I am able to send mail out of Exchange with this. Thanks!

    You can use UTL_SMTP to send mail through a server that requires authentication by sending the authentication commands after connecting. I use a procedure like the one below. I submit it using DBMS_JOB.

    CREATE OR REPLACE PROCEDURE "SEND_MAIL" (
    p_sender IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_message IN VARCHAR2
    )
    AS
    l_mailhost VARCHAR2 (255) := 'your.outgoing.mailserver.address';
    l_mail_conn UTL_SMTP.connection;
    BEGIN
    l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
    utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail UserName' ))) );
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Your Mail Password' ))) );
    UTL_SMTP.helo (l_mail_conn, l_mailhost);
    UTL_SMTP.mail (l_mail_conn, p_sender);
    UTL_SMTP.rcpt (l_mail_conn, p_recipient);
    UTL_SMTP.open_data (l_mail_conn);
    utl_smtp.write_data(l_mail_conn, 'Subject: Your Subject Line Here');
    UTL_SMTP.write_data (l_mail_conn, p_message);
    UTL_SMTP.close_data (l_mail_conn);
    UTL_SMTP.quit (l_mail_conn);
    END;

    Edited by: Brett B on Nov 17, 2008 5:15 PM
  • 11. Re: SMTP Password required
    O_X Newbie
    Currently Being Moderated
    Hi,

    i have created the send_mail proc. i call it from a button with fixed items. i get the error:


    ORA-24247: network access denied by access control list (ACL).

    i have ran this from the apex install guide:


    what else do i need to make this work. im making a call center app that needs to send an email out to the admin when a new customer is created and would like to add an email to the customer with their password info.

    thanks
    jp
  • 12. Re: SMTP Password required
    O_X Newbie
    Currently Being Moderated
    update i followed this to get past the acl error

    Re: Express with Google Maps - Error: network access denied by access control

    now i get this error:

    ORA-29279: SMTP permanent error: 503 bad sequence of commands

    my mail server im trying to use is smtp.1and1.com . how do i find out the sequence i need to enter the commands?

    thanks
    jp
  • 13. Re: SMTP Password required
    O_X Newbie
    Currently Being Moderated
    Its fixed now. here is the code i used to use a mail server that uses AUTH

    create or replace
    procedure SEND_EMAIL(
    p_to in varchar2,
    p_from in varchar2,
    p_subject in varchar2,
    p_text in varchar2 default null)
    -- p_html in varchar2 default null) -- i have this com'ed out to send plain text only
    -- p_smtp_hostname in varchar2, -- this is so i can hard code the server. will chage it to pull from company table
    -- p_smtp_portnum in varchar2) -- also will add the FROM and TO from table. to make the scheme movable.
    is
    P_smtp_hostname varchar2(255) := 'SMTP.SERVER.COM' ; -- change this to your host
    P_smtp_portnum varchar2(5) := '25' ; -- and this to right port

    l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection utl_smtp.connection;
    l_body_html clob := empty_clob; --This LOB will be the email message
    l_offset number;
    l_ammount number;
    l_temp varchar2(32767) default null;
    begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.command( l_connection, 'AUTH LOGIN');
    utl_smtp.command( l_connection, 'USER NAME IN BASE64' ); -- change this
    utl_smtp.command( l_connection, 'PASSWORD IN BASE64' ); -- and this
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );

    l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
    chr(34) || l_boundary || chr(34) || chr(13) ||
    chr(10);
    -- -- -- --- --
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);

    -- -- -- -- --
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp := '--' || l_boundary || chr(13)||chr(10);
    l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
    chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    -- -- --- --- --
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

    /** took html out for plain text emails
    -- -- -- -- --
    -- Write the HTML boundary
    l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
    chr(13) || chr(10);
    l_temp := l_temp || 'content-type: text/html;' ||
    chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    -- --- -- -- -- --
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

    **/
    -- -- -- -- -- --
    -- Write the final html boundary
    l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    -- -- -- -- -- --
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
    utl_smtp.write_data(l_connection,
    dbms_lob.substr(l_body_html,l_ammount,l_offset));
    l_offset := l_offset + l_ammount ;
    l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
    end;
  • 14. Re: SMTP Password required
    UttamSingh Newbie
    Currently Being Moderated
    hi,

    I am getting below error even though my login/pass and smtp server address are correct

    ERROR at line 1:
    ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
    ORA-06512: at "SYS.UTL_SMTP", line 21
    ORA-06512: at "SYS.UTL_SMTP", line 99
    ORA-06512: at "SYS.UTL_SMTP", line 159
    ORA-06512: at "SYSTEM.SEND_MAIL", line 12
    ORA-06512: at line 1

    pls help;

    Thanks,
    Uttam Singh
1 2 Previous Next