12 Replies Latest reply: Dec 24, 2009 3:00 AM by Sarah RSS

    UTL_SMTP in 11g DB

    ADFBeginer
      hi,
      I have a procedure for sending email from forms. The procedure is created in pl/sql.

      It is working in 9i. but due to the new security in 11g the procedure in not working.
      SEND_MAIL('sender email','recpientemail','header','message');
      I am getting error ORA-24247.

      I have checked in oracle site and found an article about this error code.

      http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-security.html

      i created the following :
      begin
      dbms_network_acl_admin.create_acl (
      acl => 'utlpkg.xml',
      description => 'Normal Access',
      principal => 'CONNECT',
      is_grant => TRUE,
      privilege => 'connect',
      start_date => null,
      end_date => null
      );
      end;



      Begin
      dbms_network_acl_admin.add_privilege (
      acl => 'utlpkg.xml',
      principal => 'myuserid',
      is_grant => TRUE,
      privilege => 'connect',
      start_date => null,
      end_date => null);
      End;


      begin
      dbms_network_acl_admin.assign_acl (
      acl => 'utlpkg.xml',
      host => 'mail.eim.ae',
      lower_port => 22,
      upper_port => 55);
      end;

      but still not working and where is in the above code saying about UTL_SMTP.
        • 1. Re: UTL_SMTP in 11g DB
          Arunkumar Ramamoorthy-Oracle
          Hi,

          What is the port of your SMTP server? I suppose it is not 25 (which is the default one) in your case.

          Can you try increasing the upper port limit?
          begin
          dbms_network_acl_admin.assign_acl (
          acl => 'utlpkg.xml',
          host => 'mail.eim.ae',
          lower_port => 1,
          upper_port => 10000);
          end;
          Also, make sure you've granted execute privilege on utl_smtp to "myuserid".

          -Arun
          • 2. Re: UTL_SMTP in 11g DB
            Sarah
            hi

            try the following link and read it properly.

            http://www.petefinnigan.com/Oracle_11g_Security_6slides.pdf


            sarah
            • 3. Re: UTL_SMTP in 11g DB
              ADFBeginer
              upper port is 10000
              grant privilege to utl_smtp to my id

              but still the same.
              • 4. Re: UTL_SMTP in 11g DB
                ADFBeginer
                Sarah,

                This is the example they have given for UTL_INADDR. Should i use the same example ?.
                Here principal => 'CC' is the username. i want this for all the users. so what entry i should use?.

                SQL> connect system/manager@ora11g
                SQL> BEGIN
                2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
                3 acl => 'simple_acl.xml',
                4 description => 'Network connection permission for
                UTL_INADDR for user CC',
                5 principal => 'CC',
                6 is_grant => TRUE,
                7 privilege => 'resolve');
                8 END;
                9 /
                SQL> BEGIN
                2 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
                3 acl => 'simple_acl.xml',
                4 host => '*');
                5 END;
                6 /
                SQL> connect cc/cc@ora11g
                SQL> exec dbms_output.put_line(utl_inaddr.get_host_name);
                • 5. Re: UTL_SMTP in 11g DB
                  Sarah
                  hi
                  Install UTL_MAIL:
                  
                  # SQLPLUS SYS AS SYSDBA
                  SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
                  SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
                  You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file. However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately.
                  
                  Ensure that you are connected as SYS using the SYSDBA privilege, and then grant the following privileges to the Database Vault Owner account.
                  
                  For example:
                  
                  CONNECT SYS/AS SYSDBA
                  Enter password: password
                  
                  GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner;
                  GRANT EXECUTE ON UTL_TCP TO dbvowner;
                  GRANT EXECUTE ON UTL_SMTP TO dbvowner;
                  GRANT EXECUTE ON UTL_MAIL TO dbvowner;
                  GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO dbvowner;
                  The UTL_TCP, UTL_SMTP, UTL_MAIL, and DBMS_NETWORK_ACL_ADMIN PL/SQL packages are used by the e-mail security alert that you will create.
                  
                  Connect to SQL*Plus as the Oracle Database Owner (DV_OWNER) account.
                  
                  For example:
                  
                  CONNECT dbvowner
                  Enter password: password
                  Create the following procedure:
                  
                  CREATE OR REPLACE PROCEDURE email_alert AS
                  msg varchar2(20000) := ‘Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: ‘;
                  BEGIN
                  msg := msg||to_char(SYSDATE, ‘Day DD MON, YYYY HH24:MI:SS’);
                  UTL_MAIL.SEND (
                  sender => ‘youremail@example.com’,
                  recipients => ‘recipientemail@example.com’,
                  subject => ‘Table modification attempted outside maintenance!’,
                  message => msg);
                  END email_alert;
                  /
                  Replace youremail@example.com with your e-mail address, and recipientemail@example.com with the e-mail address of the person you want to receive the notification
                  sarah
                  • 6. Re: UTL_SMTP in 11g DB
                    Sarah
                    hi

                    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_smtp.htm#ARPLS074


                    sarah
                    • 7. Re: UTL_SMTP in 11g DB
                      Sarah
                      hi

                      http://www.oracle-base.com/articles/11g/FineGrainedAccessToNetworkServices_11gR1.php


                      sarah
                      • 8. Re: UTL_SMTP in 11g DB
                        Sarah
                        hi

                        http://forums.tangosol.com/forums/thread.jspa?threadID=717726&tstart=45


                        :)
                        sarah
                        • 9. Re: UTL_SMTP in 11g DB
                          Andreas Weiden
                          Sarah,

                          did you ever think of putting all your information in ONE answer? ;)
                          • 10. Re: UTL_SMTP in 11g DB
                            Sarah
                            :)

                            hi

                            i was thinking sep link will be better for him :) :)


                            sarah
                            • 11. Re: UTL_SMTP in 11g DB
                              ADFBeginer
                              hi,

                              I followed your steps and i have created a procedure
                              ( sender IN VARCHAR2,
                              recipient IN VARCHAR2,
                              subject IN VARCHAR2,
                              message IN VARCHAR2)
                              IS
                              mailhost VARCHAR2(30) := 'mail.abc.ae';
                              mail_conn utl_smtp.connection;
                              crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
                              mesg VARCHAR2( 3000 );
                              BEGIN
                              mail_conn := utl_smtp.open_connection(mailhost, 25);
                              mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
                              'From: <'||sender||'>' || crlf ||
                              'Subject: '||subject || crlf ||
                              'To: '||recipient || crlf ||
                              '' || crlf || message;
                              utl_smtp.helo(mail_conn, mailhost);
                              utl_smtp.mail(mail_conn, sender);
                              utl_smtp.rcpt(mail_conn, recipient);
                              utl_smtp.data(mail_conn, mesg);
                              utl_smtp.quit(mail_conn);
                              END;


                              When i try sending a test from pl/sql i am getting error message

                              SQL> EXECUTE send_mail('adfad@abc.ae','sdrs@bddt.com','Material Delivery Delay','TESt') ;
                              ERROR at line 1:
                              ORA-06550: line 1, column 7:
                              PLS-00201: identifier 'SEND_MAIL' must be declared
                              ORA-06550: line 1, column 7:
                              PL/SQL: Statement ignored
                              • 12. Re: UTL_SMTP in 11g DB
                                Sarah
                                hi

                                try this.
                                The following example illustrates how UTL_SMTP is used by an application to send e-mail. The application connects to an SMTP server at port 25 and sends a simple text message.
                                
                                DECLARE
                                  c UTL_SMTP.CONNECTION;
                                 
                                  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
                                  BEGIN
                                    UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
                                  END;
                                 
                                BEGIN
                                  c := UTL_SMTP.OPEN_CONNECTION('smtp-server.acme.com');
                                  UTL_SMTP.HELO(c, 'foo.com');
                                  UTL_SMTP.MAIL(c, 'sender@foo.com');
                                  UTL_SMTP.RCPT(c, 'recipient@foo.com');
                                  UTL_SMTP.OPEN_DATA(c);
                                  send_header('From',    '"Sender" <sender@foo.com>');
                                  send_header('To',      '"Recipient" <recipient@foo.com>');
                                  send_header('Subject', 'Hello');
                                  UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
                                  UTL_SMTP.CLOSE_DATA(c);
                                  UTL_SMTP.QUIT(c);
                                EXCEPTION
                                  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
                                    BEGIN
                                      UTL_SMTP.QUIT(c);
                                    EXCEPTION
                                      WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
                                        NULL; -- When the SMTP server is down or unavailable, we don't have
                                              -- a connection to the server. The QUIT call will raise an
                                              -- exception that we can ignore.
                                    END;
                                    raise_application_error(-20000,
                                      'Failed to send mail due to the following error: ' || sqlerrm);
                                END;
                                Sarah