We help IT Professionals succeed at work.

record count email notification

crishna1
crishna1 asked
on
Hi,

We have an application that collects data from , say 30 input points and write into a table in a Oracle database. This means that every day at a given time 30 records are writtem into this table. Is there a way if it misses any of these records , a notification email can be sent?
Ex: today only 28 records got inserted , which means collection from 2 points is missing.

Please let me know what other information is required and i will try to provide it.

many thanks!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Easily if the database server can see a mail sewrver or mail relay.  I do something similar every day with my databases.

Check the online docs for the utl_mail package.  Installing it is 2 scripts and an spfile parameter for the mail server.

Then you can create a procedure to create a varchar2 string for whatever you want and send it.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Author

Commented:
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!

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Why wait?

It isn't that difficult.

Here is the utl_mail docs:
http://www.question-defense.com/2011/02/10/sqlplus-error-ora-12560-tnsprotocol-adapter-error

There are many examples on using it.

Once you get to where you can send an email, your 'check' script and scheduling it is easy.

Let me know when utl_mail is installed and you can send an email.

Author

Commented:
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
 



--------------------------------------------------------------------------------
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.

Author

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

Author

Commented:
i meant -- dont' i need to know , which table and column the data that needs to be verified is located?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
"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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
that will be very much appreciated, i will look forward for your script.
Have a good weekend!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Here is a scrubbed version of what I do.

I didn't run it to verify I didn't create a syntax error but I don't think I did.

The script can be tweaked to check anything you want.

For important things like my errors table, I log 'PROBLEM's and even change the subject line based on them.

So, I don't even need to open the email unless I want to look at the table counts since the subject line tells me what database it is and states OK or PROBLEM.

If OK, Most of the time I don't open them.
create or replace procedure check_db as

	sender_v		varchar2(50) := 'databaseServer@mydomain.com';
	recipient_v	varchar2(50) := 'ima_dba@mydomain.com';
	subj_v		varchar2(100);
	dbName		varchar2(50);

	crlf		varchar2(2):= CHR( 13 ) || CHR( 10 );
	tmpStr		varchar2(4000);
	message_v		varchar2(32000);

begin

	-- get the dbname for the subject line
	select name into dbName from
	(
		select substr(global_name,0,instr(global_name,'.')-1) name from global_name where global_name like '%.%'
		union
		select global_name from global_name where global_name not like '%.%'
	)
	where name is not null;

	subj_v := subj_v || dbName || ' ';

	-- 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;

	message_v := message_v || tmpStr || crlf;
	tmpStr := null;

	message_v := message_v || tmpStr || crlf;
	tmpStr := null;

	-- set subject line
	subj_v := subj_v  || to_char(sysdate,'MM/DD/YYYY') || case instr(message_v,'PROBLEM') when 0 then ': OK' else ': PROBLEM' end;

	-- send it
	utl_mail.send(
		sender => sender_v,
		recipients => recipient_v,
		subject => subj_v,
		message => message_v
	);

	exception
	when others then
		utl_mail.send(
			sender => sender_v,
			recipients => recipient_v,
			subject => 'ERROR-Checkdb: ' || SQLCODE,
			message => SQLERRM
		);
end;
/

Open in new window

Author

Commented:
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.

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.

Author

Commented:
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!

Author

Commented:
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;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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?

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Should then be something like:

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

Author

Commented:
the thing is , it adds say 30 records everyday, it needs to know how many records came in everyday i guess.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.