SQL & PL/SQL

Dear experts,

I am trying to send an email using UTL_SMTP (i switched from UTL_MAIL to UTL_SMTP since i need to send mails with large attachments - BLOB). I am using the demo_mail package given here:
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

While running the program I am getting an error message (from the error.log) saying:
[Mon Aug 04 14:00:21 2008] [error] [client 192.168.0.7] mod_plsql: /ns/email_p.send HTTP-404 ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. 9sm12723808qbw.6\nORA-06512: at "SYS.UTL_SMTP", line 20\nORA-06512: at "SYS.UTL_SMTP", line 98\nORA-06512: at "SYS.UTL_SMTP", line 221\nORA-06512: at "NEXTSTEP.SEND_EMAIL_HELPER", line 258\nORA-06512: at "NEXTSTEP.SEND_EMAIL_HELPER", line 119\nORA-06512: at "NEXTSTEP.EMAIL_P", line 33\nORA-06512: at line 31\n, referer: file:///C:/Documents and Settings/Mayank/My Documents/Flex Builder 3/ns5/bin-debug/main.swf

My code is as follows:

PACKAGE DECLARATION. This is the DEMO_MAIL package posted under above link (I have renamed it).

CREATE OR REPLACE PACKAGE NEXTSTEP.send_email_helper IS
   
  ----------------------- Customizable Section -----------------------
   
  -- Customize the SMTP host, port and your domain name below.
    smtp_host   VARCHAR2(256) := 'smtp.gmail.com';
    smtp_port   PLS_INTEGER   := 587;
    smtp_domain VARCHAR2(256) := null;

  -- Customize the signature that will appear in the email's MIME header.
  -- Useful for versioning.
  MAILER_ID   CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';
  
  --------------------- End Customizable Section ---------------------

  -- A unique string that demarcates boundaries of parts in a multi-part email
  -- The string should not appear inside the body of any part of the email.
  -- Customize this if needed or generate this randomly dynamically.
  BOUNDARY        CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

  FIRST_BOUNDARY  CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
  LAST_BOUNDARY   CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
                                              utl_tcp.CRLF;

  -- A MIME type that denotes multi-part email (MIME) messages.
  MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
                                                  BOUNDARY || '"';
  MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER   := 76 / 4 * 3;

  -- A simple email API for sending email in plain text in a single call.
  -- The format of an email address is one of these:
  --   someone@some-domain
  --   "Someone at some domain" <someone@some-domain>
  --   Someone at some domain <someone@some-domain>
  -- The recipients is a list of email addresses  separated by
  -- either a "," or a ";"
  PROCEDURE mail(sender     IN VARCHAR2,
         recipients IN VARCHAR2,
         subject    IN VARCHAR2,
         message    IN VARCHAR2);

  -- Extended email API to send email in HTML or plain text with no size limit.
  -- First, begin the email by begin_mail(). Then, call write_text() repeatedly
  -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
  -- email in non-ASCII or multi-byte character set. End the email with
  -- end_mail().
  FUNCTION begin_mail(sender     IN VARCHAR2,
              recipients IN VARCHAR2,
              subject    IN VARCHAR2,
              mime_type  IN VARCHAR2    DEFAULT 'text/plain',
              priority   IN PLS_INTEGER DEFAULT NULL)
              RETURN utl_smtp.connection;

  -- Write email body in ASCII
  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,
               message IN VARCHAR2);

  -- Write email body in non-ASCII (including multi-byte). The email body
  -- will be sent in the database character set.
  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN            VARCHAR2);
  
  -- Write email body in binary
  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN RAW);

  -- APIs to send email with attachments. Attachments are sent by sending
  -- emails in "multipart/mixed" MIME format. Specify that MIME format when
  -- beginning an email with begin_mail().
  
  -- Send a single text attachment.
  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,
            data         IN VARCHAR2,
            mime_type    IN VARCHAR2 DEFAULT 'text/plain',
            inline       IN BOOLEAN  DEFAULT TRUE,
            filename     IN VARCHAR2 DEFAULT NULL,
                last         IN BOOLEAN  DEFAULT FALSE);
  
  -- Send a binary attachment. The attachment will be encoded in Base-64
  -- encoding format.
  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,
              data         IN RAW,
              mime_type    IN VARCHAR2 DEFAULT 'application/octet',
              inline       IN BOOLEAN  DEFAULT TRUE,
              filename     IN VARCHAR2 DEFAULT NULL,
              last         IN BOOLEAN  DEFAULT FALSE);
  
  -- Send an attachment with no size limit. First, begin the attachment
  -- with begin_attachment(). Then, call write_text repeatedly to send
  -- the attachment piece-by-piece. If the attachment is text-based but
  -- in non-ASCII or multi-byte character set, use write_mb_text() instead.
  -- To send binary attachment, the binary content should first be
  -- encoded in Base-64 encoding format using the demo package for 8i,
  -- or the native one in 9i. End the attachment with end_attachment.
  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,
                 mime_type    IN VARCHAR2 DEFAULT 'text/plain',
                 inline       IN BOOLEAN  DEFAULT TRUE,
                 filename     IN VARCHAR2 DEFAULT NULL,
                 transfer_enc IN VARCHAR2 DEFAULT NULL);
  
  -- End the attachment.
  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
               last IN BOOLEAN DEFAULT FALSE);
  
  -- End the email.
  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

  -- Extended email API to send multiple emails in a session for better
  -- performance. First, begin an email session with begin_session.
  -- Then, begin each email with a session by calling begin_mail_in_session
  -- instead of begin_mail. End the email with end_mail_in_session instead
  -- of end_mail. End the email session by end_session.
  FUNCTION begin_session RETURN utl_smtp.connection;
  
  -- Begin an email in a session.
  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,
                  sender     IN VARCHAR2,
                  recipients IN VARCHAR2,
                  subject    IN VARCHAR2,
                  mime_type  IN VARCHAR2  DEFAULT 'text/plain',
                  priority   IN PLS_INTEGER DEFAULT NULL);
  
  -- End an email in a session.
  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
  
  -- End an email session.
  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;

How should I solve the above error? Can anyone help me with my query please?

If I add the code to start TLS it still gives me an error. If I add the code

utl_smtp.command(conn,'STARTTLS');
utl_smtp.helo(conn, smtp_domain);

under email_sender_help package just before under begin_session function, it gives me an error saying:

ORA-29279: SMTP permanent error: 503 5.5.1 EHLO/HELO first. s27sm2097329qbs.12


So then if i enter the same code after:
utl_smtp.command(conn,'STARTTLS');
utl_smtp.helo(conn, smtp_domain);


It gives me an error:
ORA-29278: SMTP transient error: 421 Service not available

Message was edited by:
Monk

Message was edited by:
Monk