record count email notification

Sorry for the duplicate posts.  Mobile device acting up.  If you didn't see them before I got them deleted then ignore this post.

no problem. yes, the database server can be made to see a mail server or relay.
Why dont we deal with this when you are back and not on mobile? just a thought.

i have no idea or never done this before , that way you can guide me step by step notes, this can wait till Monday.

let me know.
regards!

i see that the two scripts are as below and the init.ora parameter is SMTP_OUT_SERVER.

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

i also see three procedures, i guess you are reffering to the thrid one below?
Like i said , we can deal with this starting Monday, i have no idea how will these talk to the table and check for a value in a particular columns etc.

1) SEND Procedure
 Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients
 
2) SEND_ATTACH_RAW Procedure
 Represents the SEND Procedure overloaded for RAW attachments
 
3) SEND_ATTACH_VARCHAR2 Procedure
 Represents the SEND Procedure overloaded for VARCHAR2 attachments
 



--------------------------------------------------------------------------------

>>i see that the two scripts are as below and the init.ora parameter is SMTP_OUT_SERVER.

See, why I was typing, you have what you need.

Run those scripts as sys, set that parameter and you should be ready to go.

dont need to know , which tabe and column the dtaa that needs to be verified is located?

i meant -- dont' i need to know , which table and column the data that needs to be verified is located?

The send procedure is what you are after.

If you don't know what tables and how to query them, I cannot help.

You need to determine how you will capture the information for the subject line and message body.

"You need to determine how you will capture the information for the subject line and message body. "
how is this determined? how does it work?

i will get to know which table and exact cloumn which stores the data that needs to be verified on Monday. i need help on how to connect the above two scripts , procedure and verify the data in that column. I am on out tomorrow , the reason why i said we can look more into this next week, Monday.

The two scripts above are to install/configure utl_mail.  You only run them once and you are done.

Then you just need to create a procedure do the real work of capturing what is important to you.

If no one else posts an example, I'll scrub my script and post it tomorrow.

that will be very much appreciated, i will look forward for your script.
Have a good weekend!

Just wanted to let you know that , I am still out, due unexpected chnage of events , will try this as soon as i get a chance and let you know. most likely next week. appreciate your patience and assitance.

Hi,
1) I ran the below two scripts,
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

2) Will SET the SMTP_OUT_SERVER  as soon as i have the value available , sometime next week.
However the online document said the below, which makes me think it should be OK even if this is not set for now.
if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately

3) will get with network people  and make sure that the database server can communicate with the email server.

4) glanced thru your procedure, does it do a row count of the records inserted into a specific table on any given day and report if there is a record(s) missing?  That is what i am basically looking for , as mentioned in the original question.

Please let me know, will check back Monday.

regards!

>>and report if there is a record(s) missing?  

I don't have that need.  You can write in whatever check you want to do.  It is just sql and pl/sql.  If you can dream it, you can email it.

can you please modify/tweak the procedure and post it for me?
If that is too much to ask, i will close this question and open another Q for tweaked procedure and award more points. No rush , Monday is fine.

Let me know.
regards!

i am guessing that below is the part that needs to be modified as needed--

-- get a table count
      select rpad('My important count: ',25,' ') || to_char(count(*),'999,999,999') into tmpStr from some_important_table;
      message_v := message_v || tmpStr || crlf;
      tmpStr := null;

      -- blank line
      message_v := message_v || crlf;

      -- Check Errors table
      select case count(*) when 0 then 'OK - Errors' else 'PROBLEM - Errors' end case into tmpStr from some_error_table;

Tweak it how?

I don't know your system, structure, data, process.

If you can provide a table structure, sample data and expected results and how you get those results, I can see if I can come up with the sql for you.

It should be simple sql based on some load column.  If you expect 30 rows and get 28, how do you determine what 2 are missing?

i think it doesnt matter which 2 are missing , if it is less that 30 then it is a problem, so anything short of the total count should be notified. But will definitely have more information next week. will get back with you then.

thanks much!

Should then be something like:

Select case when count(*) = 30 then 'OK' else 'PROBLEM' end || ' with tableA' into tmpStr from tableA;

the thing is , it adds say 30 records everyday, it needs to know how many records came in everyday i guess.

So, just add a date check.  Does the table have a date_added column?

Select case when count(*) = 30 then 'OK' else 'PROBLEM' end || ' with tableA' into tmpStr from tableA where trunc(date_column) = trunc(sysdate-1);

Note: using trunc on an indexed date colu$n will not use an index but we'll address that when it becomes an issue.

Hi, this task is not immediate, have something else to finish, may get back yo this in couple of weeks, but doesnt want to leave this hanging. i am thinking of closing this , awarding the points since you already gave me the basic idea. Open another one when i am back to this in few weeks, what say?

Duplicate questions are against the rules so if you think you would ask basically the same question then leave it open.

If you have most of what you need and might need a tweak or syntax issue then you can ask a related question later.

Your call.

sure, it wont be duplicate, might need help in tweaking the procedure for our spec.
will open one for that at a later time, im sure u will remeber eventually ;-)

Thanks again for all the help!