1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

send Invalid objects list

Discussion in 'SQL PL/SQL' started by andrewscharles89, Aug 4, 2014.

  1. andrewscharles89

    andrewscharles89 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have a procedure which is used to send email about list of invalid objects .it i scheduled to run every hour after 5 mins which means(1:05,2;05,3:05...etc).so right now if there are no invalid objects also,it will send email but with empty list(bcz there are no invalid objects ).i want to send email only when the objects gets invalidated instead of sending an email with empty list every 1:05,2:05...any thoughts?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You might try sharing the code...at a guess though...why not query the number of invalid objects and bury the email routine inside an IF statement?
     
  3. andrewscharles89

    andrewscharles89 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    i used the code given in
    CREATE OR REPLACE PROCEDURE sp_send_invalid_list you can take this
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    What?

    All I'm saying is to check your list before the email procedure or the actual command...if the list is empty then don't do anything.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The code in that post is messy, and there is more than one version in the thread. However, looking at it, I see a couple of things. There is a v_count variable that is declared, but unused. Assuming your version still has it, that is convenient, because my suggestion needs one.

    Add the following line just before the FOR loop:

    Code (Text):
    v_count := 0;
    Update the loop to include a counter:

    Code (Text):
    LOOP
      v_message:=v_message||'<tr><td>'||i.owner||'</td><td>'||i. object_type||'</td></tr>'||utl_tcp.CRLF;
      v_count := v_count + 1;
    END LOOP;
    Wrap the remaining lines of the procedure in an IF statement (shades of what DTSIGuy suggested)

    Code (Text):
      IF v_count > 0 THEN
        email code.....
      END IF;