OraFAQ Forum: SQL & PL/SQL » Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle [message #609870]

Thu, 13 March 2014 07:04

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

hi,
Need help

we have table in which full day loading details are stored,now we manually capture data from that table and compose a mail send to different users.

We want to automate the process is there any way to do so.

Database :- Oracle 11g
Unix server tool used -Putty.

Please help

Report message to a moderator

Re: Oracle [message #609875 is a reply to message #609870]

Thu, 13 March 2014 08:15

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Look at DBMS_SCHEDULER.

Report message to a moderator

Re: Oracle [message #609876 is a reply to message #609875]

Thu, 13 March 2014 08:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And if you are not comfortable with DBMS_SCHEDULER you can use crontab and shell script.

Report message to a moderator

Re: Oracle [message #609879 is a reply to message #609870]

Thu, 13 March 2014 10:33

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Besides DBMS_SCHEDULER and/or cron job, you may need to create a stored procedure which would use the UTL_MAIL or UTL_SMTP pacakage(s) to send the e-mail(s).

[Updated on: Thu, 13 March 2014 11:21] by Moderator

Report message to a moderator

Re: Oracle [message #609880 is a reply to message #609879]

Thu, 13 March 2014 10:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

spool the output to a file then use mailx to send the file

Report message to a moderator

Re: Oracle [message #609881 is a reply to message #609879]

Thu, 13 March 2014 11:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As BlackSwan said (and this was what I had in mind) you don't need UTL_MAIL; you may use it but if you are comfortable with shell then it is easier to use Unix programs/commands than to learn Oracle packages (and privileges...).

Report message to a moderator

Re: Oracle [message #609882 is a reply to message #609881]

Thu, 13 March 2014 11:28

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

hi All,
Thanks for your replies.
BlackSwan can you please tell me how to do this Unix programs.
Need to get the write table data into mail body and send it to the users

Report message to a moderator

Re: Oracle [message #609884 is a reply to message #609882]

Thu, 13 March 2014 11:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

[oracle@localhost ~]$ cat spooler.sql
SPOOL emp.lis
SELECT * FROM EMP;
SPOOL OFF
EXIT

[oracle@localhost ~]$ cat sample.sh
sqlplus scott/tiger @spooler.sql
cat emp.lis > mailx -s EMP_TABLE amit_jha05@example.com

Report message to a moderator

Re: Oracle [message #609913 is a reply to message #609884]

Thu, 13 March 2014 21:18

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

hi,
I am using putty when i type cat spool.sql it gives me message as
$ cat spooler.sql
cat: spooler.sql: No such file or directory

Can you please help.

Tool Used to connect to UNIX Server -Putty

Report message to a moderator

Re: Oracle [message #609914 is a reply to message #609913]

Thu, 13 March 2014 21:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

you need to make or create the file spooler.sql

do you know how to use any command line editor on *NIX?

Report message to a moderator

Re: Oracle [message #609920 is a reply to message #609914]

Thu, 13 March 2014 22:49

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

are you referring to Vi editor

I tried
$ cat > spooler.sql
SELECT * FROM EMP;
SPOOL OFF
EXIT

If possible for you can u please provide the full code

Requirement :- Need to send mails to users body of the email will contain contain data from a table
as for example:-
Cluster Data Volume SLA Time(HH24:MM) Availability Time(YYYY/MM/DD HH24:MM) SLA Breach(Y/N) SLA Breached Time(HH:MM)
in tabular format not able to paste the same in table format.

Report message to a moderator

Re: Oracle [message #609921 is a reply to message #609920]

Thu, 13 March 2014 22:58

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Example :-

Send to abc@gmail.com
cc to :- xyz@hotmail.com

Subject :- Refresh Status at System Date

Body :-
Please find details below
Cluster Data Volume SLA Time(HH24:MM) Availability Time(YYYY/MM/DD HH24:MM) SLA Breach(Y/N) SLA Breached Time(HH:MM)
in tabular format not able to paste the same in table format.

Report message to a moderator

Re: Oracle [message #609922 is a reply to message #609920]

Thu, 13 March 2014 23:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

it appears that you are unknowledgable on all things related to *NIX, SQL, & Oracle

I'll cease to contribute more to this thread & will allow others to continue to do your job for you without interference from me.

Report message to a moderator

Re: Oracle [message #609923 is a reply to message #609914]

Thu, 13 March 2014 23:03

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

hi,
are you referring to Vi editor

I tried
$ cat > spooler.sql
SELECT * FROM EMP;
SPOOL OFF
EXIT

If possible for you can u please provide the full code

Requirement :- Need to send mails to users body of the email will contain contain data from a table
as for example:-
Cluster Data Volume SLA Time(HH24:MM) Availability Time(YYYY/MM/DD HH24:MM) SLA Breach(Y/N) SLA Breached Time(HH:MM)
in tabular format not able to paste the same in table format.

Send to abc@gmail.com
cc to :- xyz@hotmail.com

Subject :- Refresh Status at System Date

Body :-
Please find details below
Cluster Data Volume SLA Time(HH24:MM) Availability Time(YYYY/MM/DD HH24:MM) SLA Breach(Y/N) SLA Breached Time(HH:MM)
This data comes from a table in database (as select * from EMP tbale)
in tabular format not able to paste the same in table format.

Report message to a moderator

Re: Oracle [message #609928 is a reply to message #609923]

Fri, 14 March 2014 01:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The requirements are clear, the way to do it has been given, now if you are unable to do it just tell it to your boss so he can ask someone else to do the job in time.

Report message to a moderator

Re: Oracle [message #609957 is a reply to message #609928]

Fri, 14 March 2014 11:16

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

I can tell that to my boss thanks for the advice but i thought this forum is for help and not such advises

Report message to a moderator

Re: Oracle [message #609958 is a reply to message #609957]

Fri, 14 March 2014 11:23

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

This forum is to HELP people do their jobs. Not to DO their jobs for them.

The same way you can ask someone on the street for directions, but when you start to ask them how to operate your vehicle then there is not much hope that you make it to your destination before you take some driving classes.

Report message to a moderator

Previous Topic:	Efficient way to replace HTML codes with special characters
Next Topic:	Jpublisher is giving error while generating the class ? ? ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 15:22:46 CDT 2024