1 2 3 Previous Next 36 Replies Latest reply on Oct 26, 2007 4:44 PM by 589737 Go to original post
      • 15. Re: How to send of dbms_output.putline using utl_smtp
        Sentinel
        Make sure that your utl_smtp.open_data(v_connection); is outside of your loop currently it's inside your loop.

        Otherwise you are going to be reopening the data section of your email multiple times and I have no idea how oracle or your SMTP server is going to react to that.

        Have you tried creating a simple test procedure to be sure that you can send mail from oracle?

        There is an example anonymous PS/SQL block that sends a hello world email message in the utl_smtp package spec.

        You may also want to use the function versions of the UTL_SMTP calls so that you can double check the replies being generated by the SMTP server you are connecting to. It's possible that your SMTP server is rejecting some portion of the email transaction without causing an exception in oracle.
        • 16. Re: How to send of dbms_output.putline using utl_smtp
          salvelinus fontinalis
          /*Test for results of select. For example, if you get null results, you don't know it and then you concatenate to a null result, again resulting in NULL.

          Make an email package to do the work. It contains all the stuff like make a connection, say hello, etc. It also includes a function that accepts something like 3 parameters

          ToAddress, Message , Subject

          and it does the rest. You can make overloaded versions of all these if you need to target a particular SMTP mailhost or whatever.

          Then each bit is testable and you can put in exception handling.

          This package is reusable all over the place.

          Once all that is done, your primary procedure can be much simpler something like:
          */

          -- WHY A DATE NUMBER? Perhaps you could explain this parameter logic too? Shouldn't it be a date type maybe?
          CREATE OR REPLACE PROCEDURE Sp_Data_Check(p_Date NUMBER) IS

          --DECLARE
          v_Sqlstring VARCHAR2(1024);
          v_Result PLS_INTEGER; -- recommended;
          Ll_Hits PLS_INTEGER := 0; -- number of tables meeting test criteria
          v_Output VARCHAR2(40); -- just holds table_name (30) plus two characters TABLE:0
          Ls_Message VARCHAR2(4000) := ''; -- holds like 100 table messages
          Crlf CONSTANT VARCHAR2(2) := Chr(13) || Chr(10);

          BEGIN

          FOR Irec IN (SELECT Table_Name
          FROM User_Tab_Columns
          WHERE Column_Name = 'REPORT_DATE'
          AND Table_Name NOT LIKE '%BIN%')
          LOOP

          v_Sqlstring := 'select decode(max(report_date,' || p_Date ||
          ',1,0))' || ' from ' || Irec.Table_Name;

          BEGIN
          EXECUTE IMMEDIATE v_Sqlstring
          INTO v_Result;
          EXCEPTION
          -- CHECK RESULT!

          WHEN No_Data_Found THEN
          RAISE; -- OH NO!
          WHEN OTHERS THEN
          RAISE; -- OH NO!
          END;

          v_Output := Irec.Table_Name || ':' || To_Char(v_Result);

          Dbms_Output.Put_Line(v_Output);

          IF v_Result = 0
          THEN
          Ll_Hits := Ll_Hits + 1; --HOW MANY HITS?  MAYBE YOU WANT TO KNOW?
          Ls_Message := Ls_Message || v_Output || Crlf;
          END IF;

          END LOOP;
          /*
          if length( ls_message ) > 0 then -- again, may want to see if ls_message is NULL!

          -- then call a one liner in your email package PKG_EMAIL to send the notification
          if pkg_email.f_email('recipients',ls_message,'Latest modifications by table') then
          -- do something if it fails!!!!!
          end if

          --or

          -- .pkg_email.pEmail( TO, FROM, ls_message);
          */
          EXCEPTION
          WHEN OTHERS THEN
          RAISE; -- OH NO!
          END;
          • 17. Re: How to send of dbms_output.putline using utl_smtp
            William Robertson
            > Actually, I am useing sqldeveloper and the procedure compiled fine.

            No, Salvelinus was suggesting you run it through a debugger (such as the one in SQL Developer) in order to check what it is doing at runtime, examine variable values, watch loop iterations etc.

            I agree with the other suggestion to create a test email procedure to make sure you can send an email from the database without all this dynamic SQL and looping getting in the way.

            @salvelinus fontinalis, wouldn't the WHEN OTHERS THEN RAISE; statements as posted just obscure the source of the exception by changing the line number?
            • 18. Re: How to send of dbms_output.putline using utl_smtp
              salvelinus fontinalis
              The exception handling is lame. Just for example purposes.
              • 19. Re: How to send of dbms_output.putline using utl_smtp
                589737
                Actually, i think i finally got it to work. First off, here is my mail program (Please let me know if it is correct):

                create or replace
                PROCEDURE utl_sendmail
                (
                message IN VARCHAR2
                ) IS

                crlf VARCHAR2(2):= UTL_TCP.CRLF;
                connection utl_smtp.connection;
                mailhost VARCHAR2(30) := 'localhost';
                sender varchar2(30):= 'mail@localhost';
                subject varchar2(30):= 'Report Date Check';
                recipient varchar2(30):= 'emailaddy@domain.com';
                header VARCHAR2(1000);

                BEGIN

                --
                -- Start the connection.
                --
                connection := utl_smtp.open_connection(mailhost);

                header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
                'From: '||sender||''||crlf||
                'Subject: '||subject||crlf||
                'To: '||recipient;

                --
                -- Handshake with the SMTP server
                --
                utl_smtp.helo(connection, mailhost);
                utl_smtp.mail(connection, sender);
                utl_smtp.rcpt(connection, recipient);
                utl_smtp.open_data(connection);
                --
                -- Write the header
                --
                utl_smtp.write_data(connection, header);
                --
                -- The crlf is required to distinguish that what comes next is not simply part of the header..
                --
                utl_smtp.write_data(connection, crlf ||message);
                utl_smtp.close_data(connection);
                utl_smtp.quit(connection);

                EXCEPTION
                WHEN UTL_SMTP.INVALID_OPERATION THEN
                dbms_output.put_line(' Invalid Operation in SMTP transaction.');
                WHEN UTL_SMTP.TRANSIENT_ERROR THEN
                dbms_output.put_line(' Temporary problems with sending email - try again later.');
                WHEN UTL_SMTP.PERMANENT_ERROR THEN
                dbms_output.put_line(' Errors in code for SMTP transaction.');
                END;


                Now here is my procedure that is checking the report_date column of all my tables to see if the report_date is equal to the p_date or not. If the v_result is 0 then i want the mail program to email me the v_output

                create or replace
                PROCEDURE sp_data_check(p_date number) is
                --DECLARE
                v_table_name varchar2(35);
                     v_string varchar2(1024);
                     v_result number;
                     v_output varchar2(1024);

                     CURSOR c_table is
                          select table_name
                          from user_tab_columns
                          where COLUMN_NAME = 'REPORT_DATE'
                          and table_name NOT LIKE '%BIN%';
                     BEGIN
                OPEN c_table;
                     loop
                          FETCH c_table into v_table_name;
                          exit when c_table%NOTFOUND;
                          v_string:='select decode(max(report_date),'||p_date||',1,0)'|| ' from ' || v_table_name;
                          execute immediate v_string into v_result;
                          v_output:=v_table_name||':'||v_result;
                if v_result =0 then
                utl_sendmail(v_output);
                end if;
                end loop;
                close c_table;
                END;



                This does not seem to work as I am getting an email, but there is no information in the body of the email that i receive. I know the procedure works because when i do a dbms_output.put_line(v_output), i get the following results:


                Connecting to the database willie.
                POPULATION_VERSION_STATS:0
                POPULATION_HW_STATS:0
                POPULATION_STATS:0
                DIAG_STATS:0
                MODULE_VISIT_STATS:0
                Process exited.
                Disconnecting from the database willie.


                The problem i think maybe that i have my utl_sendmail(v_output) in the loop. How do i go around this being that i need to have my if condition in the loop?

                Almost there guys!
                • 20. Re: How to send of dbms_output.putline using utl_smtp
                  ajallen
                  You do not seem to mention your Oracle release. However, if you are using 10g, this whole thing becomes super easy. All you need to is install utl_mail.
                  • 21. Re: How to send of dbms_output.putline using utl_smtp
                    AlokKumar
                    If you happen to be ride on 10g, then utl_smtp is a gonner and you can install utl_mail, which is simply a child's play in order to send a mail.

                    hare krishna
                    Alok
                    • 22. Re: How to send of dbms_output.putline using utl_smtp
                      589737
                      Our database is 10g. How do i know if utl_mail is installed or not? What would i have to do different than what i have already done with utl_smtp.
                      • 23. Re: How to send of dbms_output.putline using utl_smtp
                        ajallen
                        do a describe of sys.utl_mail to see if it exists. If not you can install it easy.
                        login as sysdba and
                        @?/rdbms/admin/utlmail.sql
                        to create the package header, then
                        @?/rdbms/admin/prvtmail.plb
                        to create the package body.

                        Sample proc to send mail
                        PROCEDURE send_mail_message ;
                        IS
                          l_sender_email     VARCHAR2(50);
                          l_recipients_email VARCHAR2(500);
                          l_subject_line     VARCHAR2(200);
                          l_message_body     VARCHAR2(4000)  DEFAULT NULL;
                        BEGIN

                        -- ----------------
                        -- Setup mail arguments
                        -- ----------------
                        l_sender_email     := 'some.one@myfirm.com';
                        l_recipients_email := 'someone.else@myfirm.com';
                        l_subject_line     := 'Message for today';
                        l_message_body     := 'Stuff to tell your folks';

                        -- ----------------
                        -- Send the message
                        -- ----------------
                          BEGIN
                            UTL_MAIL.SEND( SENDER      => l_sender_email,
                                           RECIPIENTS  => l_recipients_email,
                                           CC          => NULL,
                                           BCC         => NULL,
                                           SUBJECT     => l_subject_line,
                                           MESSAGE     => l_message_body,
                                           MIME_TYPE   => 'text/plain; charset=us-ascii',
                                           PRIORITY    => NULL );
                          EXCEPTION
                            WHEN others THEN
                            -- log execption here
                            -- reraise exception;
                            RAISE;
                          END;

                        -- Fin

                        END send_mail_message;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                        • 24. Re: How to send of dbms_output.putline using utl_smtp
                          Sentinel
                          You are missing a CRLF after your header section. You don't have one trailing the recipient, so the CRLF leading your message body is not actually putting a blank line between your header section and the body text. If you can take a detailed look at the actual email you are recieving you should see all of your body text in the header of the mail.

                          Just add the missing CRLF after your recipient or better yet use a function for writing the headers as suggested by in the UTL_SMTP package spec that adds the CRLF for you.

                          Also as suggested elsewhere the UTL_MAIL package could be a good alternative as it encapsulates a lot of what you need to do with UTL_SMTP. But then again you are almost there with the UTL_SMTP package itself.
                          • 25. Re: How to send of dbms_output.putline using utl_smtp
                            589737
                            Thanks for your help everyone. I installed UTL_MAIL and i like it. The only downside is that there are no arguments within that package i can use for EXCEPTION handeling. Any thoughts on this.
                            • 26. Re: How to send of dbms_output.putline using utl_smtp
                              589737
                              I have created the following procedure and getting an error when trying to compile it. I am using sql*developer.

                              create or replace
                              PROCEDURE utl_send_mail(
                              l_sender_email VARCHAR2(50);
                              l_recipients_email VARCHAR2(500);
                              l_subject_line VARCHAR2(200);
                              l_message_body VARCHAR2(4000);
                              ) IS
                              BEGIN
                              UTL_MAIL.SEND( SENDER => l_sender_email,
                              RECIPIENTS => l_recipients_email,
                              CC => NULL,
                              BCC => NULL,
                              SUBJECT => l_subject_line,
                              MESSAGE => l_message_body,
                              MIME_TYPE => 'text/plain; charset=us-ascii',
                              PRIORITY => NULL);
                              END utl_send_mail
                              ;

                              Error(3,30): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character

                              null
                              • 27. Re: How to send of dbms_output.putline using utl_smtp
                                William Robertson
                                You have semicolons between parameters instead of commas.
                                • 28. Re: How to send of dbms_output.putline using utl_smtp
                                  589737
                                  it still doesn't work. still getting error PLS-00103.

                                  create or replace
                                  PROCEDURE sp_send_mail(
                                  p_sender VARCHAR2(50),
                                  p_recipient VARCHAR2(50),
                                  p_subject VARCHAR2(200),
                                  p_message VARCHAR2(4000)
                                  ) is
                                  BEGIN
                                  UTL_MAIL.SEND( SENDER => p_sender,
                                  RECIPIENTS => p_recipient,
                                  CC => NULL,
                                  BCC => NULL,
                                  SUBJECT => p_subject,
                                  MESSAGE => p_message,
                                  MIME_TYPE => 'text/plain; charset=us-ascii',
                                  PRIORITY => NULL);
                                  END sp_send_mail;



                                  Error(3,24): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
                                  • 29. Re: How to send of dbms_output.putline using utl_smtp
                                    589737
                                    now, when i modified my procedure to look more like this it works. Does anyone know why? Refer to previous post for previous version that was not working. My guess is you cannot specify size in the parameters.


                                    create or replace
                                    PROCEDURE sp_send_mail(
                                    p_sender VARCHAR2,
                                    p_recipient VARCHAR2,
                                    p_subject VARCHAR2,
                                    p_message VARCHAR2
                                    ) is
                                    BEGIN
                                    UTL_MAIL.SEND( SENDER => p_sender,
                                    RECIPIENTS => p_recipient,
                                    CC => NULL,
                                    BCC => NULL,
                                    SUBJECT => p_subject,
                                    MESSAGE => p_message,
                                    MIME_TYPE => 'text/plain; charset=us-ascii',
                                    PRIORITY => NULL);
                                    END sp_send_mail;