Sending sql query result by mail

TravisBrooker · 06-04-2021, 06:24 AM

Dear all,
i can make a sql connection to db with sqlplus
and after i connect, i write a query. i want to make a script to get this query result in every 2 hour.

how can i do that?

thank you.

wpeckham · 06-04-2021, 07:26 AM

One (my) solution:

Wrap your query in a shell script, pipe your query output to a file, have your script fire up an email client to send and email as you require with that file attached.

If the output is small, you can html wrap it and make it part of the body of the email. How useful this is might depend partly upon what email client you READ it in, so testing your solution should include reading tests with any different clients you might use.

TravisBrooker · 06-04-2021, 07:31 AM

Quote:

Originally Posted by wpeckham

Wrap your query in a shell script, pipe your query output to a file, have your script fire up an email client to send and email as you require with that file attached.

If the output is small, you can html wrap it and make it part of the body of the email. How useful this is might depend partly upon what email client you READ it in, so testing your solution should include reading tests with any different clients you might use.

hi there, thank you for the answer.

i only want to connect sql by sqlplus ***/***@10.200.200.200:1561/ser

and then i want to run this command : select * from SESSION;

and i only want to send the output of this query result by mail. thats all i want.

boughtonp · 06-04-2021, 07:47 AM

Ok, so what exactly have you tried, and where in the process are you stuck?

Have you read any of the sqlplus user guide or performed any searches?

https://www.linuxquestions.org/questions/faq.php?faq=welcome

TravisBrooker · 06-04-2021, 07:53 AM

Quote:

Originally Posted by boughtonp

Ok, so what exactly have you tried, and where in the process are you stuck?

Have you read any of the sqlplus user guide or performed any searches?

https://www.linuxquestions.org/questions/faq.php?faq=welcome

it worked, i got the result as an output. only i want this output to send me by mail every 2 hour. only i couldnt do that.

boughtonp · 06-04-2021, 08:01 AM

Quote:

Originally Posted by TravisBrooker

it worked, i got the result as an output. only i want this output to send me by mail every 2 hour. only i couldnt do that.

Then consult your previous threads where you have examples of both sending emails and using cron!

You should also read and understand all of the following, but especially the emphasized parts:

Quote:

Originally Posted by https://www.linuxquestions.org/questions/faq.php?faq=welcome

Hello and welcome to LQ. You were pointed here because a fellow LQ member feels the thread you started doesn't meet the minimum threshold that would enable us to help you. We understand that Linux can be intimidating for new members, and we really do want to help. That said, please understand that LQ is not a help desk, customer service line for a product you purchased or willing to do your homework (although we are happy to assist you with specifics, if you show some effort of your own!). We're a 100% volunteer organization that wants to help you help yourself.

Here are a couple tips that will enable us to help you moving forward:

When asking a question, be sure to provide as many relevant details as possible. You should include the distribution and version you're using, along with hardware details, application version and exact error messages where applicable.
Using a descriptive title will vastly increase the number of members who see your thread, and therefore make a response significantly more likely.
If you're actively troubleshooting an issue you should also include any steps you've already taken.
You may want to include how what you are experiencing differs from what you expected to experience.
Before posting, have you used the search function to ensure your question hasn't been asked before?
You can edit your post by clicking the EDIT button in the bottom right corner of your message.

If you are unwilling or unable to ask questions in a manner that allows us to help you, it's unlikely our community will be able to provide you a solution. Unfortunately, serial offenders who show wanton disregard for this request after multiple pointers may be asked to seek help elsewhere. We truly hope that isn't necessary, and assure you Linux and Open Source are extremely rewarding and well worth the learning curve in the long run.

jmgibson1981 · 06-04-2021, 08:49 AM

Here is a function I use to send emails to myself from my server. Should get you started. Uses msmtp.

Code:

emergency_email_func() {
	EMAILLOC=/tmp/"$(uname -n)"."$2"."$NOW".alert
	echo "To: ${1}
From: $(grep from "$HOME"/.msmtprc | cut -d' ' -f 2)
Subject: $(uname -n) alert!

" > "$EMAILLOC"
	echo "snapraid scrub finished with errors. check manually" >> "$EMAILLOC"
	msmtp "$1" < "$EMAILLOC"
	rm "$EMAILLOC"
}

chrism01 · 06-09-2021, 12:54 AM

Here's a basic one-liner you can expand on

Code:

(shell script)| mailx -s "Email subject" <your email addr>

Basically, this runs your code in a sub-shell, whose o/p is captured and sent as the body of an email with subject you specify in quotes, then your email.