Home » SQL & PL/SQL » SQL & PL/SQL » how to send tablespace mail alerts with PL/SQL
how to send tablespace mail alerts with PL/SQL [message #661280] |
Mon, 13 March 2017 21:45 |
|
diomahardhika
Messages: 18 Registered: March 2017
|
Junior Member |
|
|
create or replace procedure sending_mail2(
v_sender in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
v_recipient in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
v_subject in varchar2 := 'tablespace',
v_message in clob := ' ',
v_cc in varchar2 := 'nugraha.p.perdana@intra.sri-astra.com')
is
v_connection utl_smtp.connection;
v_host varchar2(60) := 'intra@sri-astra.com';
v_query varchar2(2000);
v_body varchar2(10000);
tablespace_name varchar(60);
total number(25);
used number(25);
free number(25);
pct number(25);
begin
v_query := 'select tbs.tablespace_name,
tot.bytes / 1024 total,
tot.bytes / 1024 -sum(nvl(fre.bytes, 0)) / 1024 used,
sum(nvl(fre.bytes, 0)) / 1024 free,
round((1 -sum(nvl(fre.bytes, 0)) / tot.bytes), 4) *100 pct
into tablespace_name, total, used, free, pct;
from dba_free_space fre,
(select tablespace_name,
sum(bytes)bytes
from dba_data_files
group by tablespace_name)
tot,
dba_tablespaces tbs
where tbs.tablespace_name = tot.tablespace_name
and fre.tablespace_name(+)= tot.tablespace_name
and v_message = v_body
group by tbs.tablespace_name,
tot.bytes / 1024,
tot.bytes
order by 5, 1';
execute IMMEDIATE v_query INTO tablespace_name, total, used, free, pct, v_body;
v_connection := utl_smtp.open_connection(v_host, 25);
utl_smtp.helo(v_connection, v_host);
utl_smtp.mail(v_connection, v_sender);
utl_smtp.rcpt(v_connection, v_recipient);
utl_smtp.open_data(v_connection);
utl_smtp.write_data(v_connection,'Date:'|| to_date('14032017','ddmmyyyy') || utl_tcp.crlf);
utl_smtp.write_data(v_connection,'From:'|| v_sender||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'To:'|| v_recipient||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'cc:'||v_cc||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'Subject:'||v_subject||utl_tcp.crlf||
utl_tcp.crlf||v_message);
utl_smtp.close_data(v_connection);
utl_smtp.quit(v_connection);
end ;
when i execute that code i've got an error:
identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
anybody can help me how to fix this?
[Edit MC: add code tags]
[Updated on: Tue, 14 March 2017 01:16] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661292 is a reply to message #661291] |
Tue, 14 March 2017 01:22 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Like that:
SQL> create or replace procedure sending_mail2(
2 v_sender in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
3 v_recipient in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
4 v_subject in varchar2 := 'tablespace',
5 v_message in clob := ' ',
6 v_cc in varchar2 := 'nugraha.p.perdana@intra.sri-astra.com')
7
8 is
9 v_connection utl_smtp.connection;
10 v_host varchar2(60) := 'intra@sri-astra.com';
11 v_query varchar2(2000);
12 v_body varchar2(10000);
13 tablespace_name varchar(60);
14 total number(25);
15 used number(25);
16 free number(25);
17 pct number(25);
18
19 begin
20 v_query := 'select tbs.tablespace_name,
21 tot.bytes / 1024 total,
22 tot.bytes / 1024 -sum(nvl(fre.bytes, 0)) / 1024 used,
23 sum(nvl(fre.bytes, 0)) / 1024 free,
24 round((1 -sum(nvl(fre.bytes, 0)) / tot.bytes), 4) *100 pct
25 into tablespace_name, total, used, free, pct;
26
27 from dba_free_space fre,
28 (select tablespace_name,
29 sum(bytes)bytes
30 from dba_data_files
31 group by tablespace_name)
32 tot,
33 dba_tablespaces tbs
34 where tbs.tablespace_name = tot.tablespace_name
35 and fre.tablespace_name(+)= tot.tablespace_name
36 and v_message = v_body
37 group by tbs.tablespace_name,
38 tot.bytes / 1024,
39 tot.bytes
40 order by 5, 1';
41
42 execute IMMEDIATE v_query INTO tablespace_name, total, used, free, pct, v_body;
43
44 v_connection := utl_smtp.open_connection(v_host, 25);
45 utl_smtp.helo(v_connection, v_host);
46 utl_smtp.mail(v_connection, v_sender);
47 utl_smtp.rcpt(v_connection, v_recipient);
48
49 utl_smtp.open_data(v_connection);
50 utl_smtp.write_data(v_connection,'Date:'|| to_date('14032017','ddmmyyyy') || utl_tcp.crlf);
51 utl_smtp.write_data(v_connection,'From:'|| v_sender||utl_tcp.crlf);
52 utl_smtp.write_data(v_connection,'To:'|| v_recipient||utl_tcp.crlf);
53 utl_smtp.write_data(v_connection,'cc:'||v_cc||utl_tcp.crlf);
54 utl_smtp.write_data(v_connection,'Subject:'||v_subject||utl_tcp.crlf||
55 utl_tcp.crlf||v_message);
56 utl_smtp.close_data(v_connection);
57 utl_smtp.quit(v_connection);
58 end ;
59 /
Procedure created.
SQL> begin
2 sending_mail2;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "MICHEL.SENDING_MAIL2", line 42
ORA-06512: at line 2
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661295 is a reply to message #661293] |
Tue, 14 March 2017 01:36 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The error is there:
25 into tablespace_name, total, used, free, pct;
You don't need EXECUTE IMMEDIATE here, this is for DYNAMIC statement and yours is static:
SQL> create or replace procedure sending_mail2(
2 v_sender in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
3 v_recipient in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
4 v_subject in varchar2 := 'tablespace',
5 v_message in clob := ' ',
6 v_cc in varchar2 := 'nugraha.p.perdana@intra.sri-astra.com')
7
8 is
9 v_connection utl_smtp.connection;
10 v_host varchar2(60) := 'intra@sri-astra.com';
11 v_query varchar2(2000);
12 v_body varchar2(10000);
13 tablespace_name varchar(60);
14 total number(25);
15 used number(25);
16 free number(25);
17 pct number(25);
18
19 begin
20 select tbs.tablespace_name,
21 tot.bytes / 1024 total,
22 tot.bytes / 1024 -sum(nvl(fre.bytes, 0)) / 1024 used,
23 sum(nvl(fre.bytes, 0)) / 1024 free,
24 round((1 -sum(nvl(fre.bytes, 0)) / tot.bytes), 4) *100 pct
25 into tablespace_name, total, used, free, pct
26
27 from dba_free_space fre,
28 (select tablespace_name,
29 sum(bytes)bytes
30 from dba_data_files
31 group by tablespace_name)
32 tot,
33 dba_tablespaces tbs
34 where tbs.tablespace_name = tot.tablespace_name
35 and fre.tablespace_name(+)= tot.tablespace_name
36 -- and v_message = v_body <-- WHAT IS THIS?
37 group by tbs.tablespace_name,
38 tot.bytes / 1024,
39 tot.bytes
40 order by 5, 1;
41
42 -- execute IMMEDIATE v_query INTO tablespace_name, total, used, free, pct, v_body;
43
44 v_connection := utl_smtp.open_connection(v_host, 25);
45 utl_smtp.helo(v_connection, v_host);
46 utl_smtp.mail(v_connection, v_sender);
47 utl_smtp.rcpt(v_connection, v_recipient);
48
49 utl_smtp.open_data(v_connection);
50 utl_smtp.write_data(v_connection,'Date:'|| to_date('14032017','ddmmyyyy') || utl_tcp.crlf);
51 utl_smtp.write_data(v_connection,'From:'|| v_sender||utl_tcp.crlf);
52 utl_smtp.write_data(v_connection,'To:'|| v_recipient||utl_tcp.crlf);
53 utl_smtp.write_data(v_connection,'cc:'||v_cc||utl_tcp.crlf);
54 utl_smtp.write_data(v_connection,'Subject:'||v_subject||utl_tcp.crlf||
55 utl_tcp.crlf||v_message);
56 utl_smtp.close_data(v_connection);
57 utl_smtp.quit(v_connection);
58 end ;
59 /
Procedure created.
SQL> exec sending_mail2;
BEGIN sending_mail2; END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MICHEL.SENDING_MAIL2", line 20
ORA-06512: at line 1
Now you have to loop on the result and not just get it in variables.
[Updated on: Tue, 14 March 2017 01:40] Report message to a moderator
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661300 is a reply to message #661297] |
Tue, 14 March 2017 01:47 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes, something like:
for rec in (
select tbs.tablespace_name,
tot.bytes / 1024 total,
tot.bytes / 1024 -sum(nvl(fre.bytes, 0)) / 1024 used,
sum(nvl(fre.bytes, 0)) / 1024 free,
round((1 -sum(nvl(fre.bytes, 0)) / tot.bytes), 4) *100 pct
into tablespace_name, total, used, free, pct
from dba_free_space fre,
(select tablespace_name,
sum(bytes)bytes
from dba_data_files
group by tablespace_name)
tot,
dba_tablespaces tbs
where tbs.tablespace_name = tot.tablespace_name
and fre.tablespace_name(+)= tot.tablespace_name
-- and v_message = v_body <-- WHAT IS THIS?
group by tbs.tablespace_name,
tot.bytes / 1024,
tot.bytes
order by 5, 1
) loop
v_message := v_message || utl_tcp.crlf || '... something with the result ...';
end loop;
PL/SQL User's Guide and Reference
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661303 is a reply to message #661295] |
Tue, 14 March 2017 01:56 |
|
diomahardhika
Messages: 18 Registered: March 2017
|
Junior Member |
|
|
create or replace procedure sending_mail2(
v_sender in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
v_recipient in varchar2 := 'dio.mahardhika@intra.sri-astra.com',
v_subject in varchar2 := 'tablespace',
v_message in clob := ' ',
v_cc in varchar2 := 'nugraha.p.perdana@intra.sri-astra.com')
is
v_connection utl_smtp.connection;
v_host varchar2(60) := 'intra@sri-astra.com';
v_query varchar2(2000);
v_body varchar2(10000);
tablespace_name varchar(60);
total number(25);
used number(25);
free number(25);
pct number(25);
begin
for rec in(
select tbs.tablespace_name,
tot.bytes / 1024 total,
tot.bytes / 1024 -sum(nvl(fre.bytes, 0)) / 1024 used,
sum(nvl(fre.bytes, 0)) / 1024 free,
round((1 -sum(nvl(fre.bytes, 0)) / tot.bytes), 4) *100 pct
into tablespace_name, total, used, free, pct
from dba_free_space fre,
(select tablespace_name,
sum(bytes)bytes
from dba_data_files
group by tablespace_name)
tot,
dba_tablespaces tbs
where tbs.tablespace_name = tot.tablespace_name
and fre.tablespace_name(+)= tot.tablespace_name
-- and v_message = v_body
group by tbs.tablespace_name,
tot.bytes / 1024,
tot.bytes
order by 5, 1) loop
v_message := v_message || utl_tcp.crlf || 'hola';
end loop;
-- execute IMMEDIATE v_query INTO tablespace_name, total, used, free, pct, v_body;
v_connection := utl_smtp.open_connection(v_host, 25);
utl_smtp.helo(v_connection, v_host);
utl_smtp.mail(v_connection, v_sender);
utl_smtp.rcpt(v_connection, v_recipient);
utl_smtp.open_data(v_connection);
utl_smtp.write_data(v_connection,'Date:'|| to_date('14032017','ddmmyyyy') || utl_tcp.crlf);
utl_smtp.write_data(v_connection,'From:'|| v_sender||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'To:'|| v_recipient||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'cc:'||v_cc||utl_tcp.crlf);
utl_smtp.write_data(v_connection,'Subject:'||v_subject||utl_tcp.crlf||
utl_tcp.crlf||v_message);
utl_smtp.close_data(v_connection);
utl_smtp.quit(v_connection);
end ;
Error(42,23): PLS-00363: expression 'V_MESSAGE' cannot be used as an assignment target
|
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661317 is a reply to message #661305] |
Tue, 14 March 2017 08:23 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
personally I would have your DBA install the UTL_MAIL package. It is very easy to install and it really simplifies send emails . The install instructions are below and the suggested code follows that
To install UTL_MAIL:
sqlplus sys/<PASSWORD> as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.sql
SQL> alter system set smtp_out_server='mail.MYDOMAIN.COM' scope=both;
SQL> GRANT EXECUTE ON UTL_MAIL TO USERS_SCHEMA;
CREATE OR REPLACE PROCEDURE Sending_mail2 (
V_sender IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_recipient IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_subject IN VARCHAR2 := 'tablespace',
V_cc IN VARCHAR2 := 'nugraha.p.perdana@intra.sri-astra.com')
IS
V_body VARCHAR2 (32767);
Tablespace_name VARCHAR (60);
BEGIN
FOR Rec
IN ( SELECT Tbs.Tablespace_name,
Tot.Bytes / 1024 Total,
Tot.Bytes / 1024 - SUM (NVL (Fre.Bytes, 0)) / 1024 Used,
SUM (NVL (Fre.Bytes, 0)) / 1024 Free,
ROUND ( (1 - SUM (NVL (Fre.Bytes, 0)) / Tot.Bytes), 4)
* 100
Pct
FROM Dba_free_space Fre,
( SELECT Tablespace_name, SUM (Bytes) Bytes
FROM Dba_data_files
GROUP BY Tablespace_name) Tot,
Dba_tablespaces Tbs
WHERE Tbs.Tablespace_name = Tot.Tablespace_name
AND Fre.Tablespace_name(+) = Tot.Tablespace_name
GROUP BY Tbs.Tablespace_name, Tot.Bytes / 1024, Tot.Bytes
ORDER BY 5 DESC, 1)
LOOP
V_message :=
V_body
|| Rec.Tablespace_name
|| ' | '
|| TO_CHAR (Rec.Total)
|| ' | '
|| TO_CHAR (Rec.Used)
|| ' | '
|| TO_CHAR (Rec.Free)
|| ' | '
|| TO_CHAR (Rec.Pct)
|| UTL_TCP.Crlf;
END LOOP;
UTL_MAIL.Send (Sender => V_sender,
Recipients => V_recipient,
Cc => V_cc,
Subject => V_subject,
MESSAGE => V_subject);
END;
[Updated on: Tue, 14 March 2017 08:25] Report message to a moderator
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661564 is a reply to message #661317] |
Thu, 23 March 2017 20:21 |
|
diomahardhika
Messages: 18 Registered: March 2017
|
Junior Member |
|
|
thank you for your help, im very appreciate that and once again thanks,
im just modified the query like this :
create or replace PROCEDURE Sending_mail2 (
V_sender IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_recipient IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_cc IN VARCHAR2 := 'nugraha.p.perdana@intra.sri-astra.com',
V_subject IN VARCHAR2 := 'Tablespace')
IS
V_body VARCHAR2 (32767);
Tablespace_name VARCHAR (60);
V_Message VARCHAR2(32767);
V_Host VARCHAR2(60) := 'intra.sri-astra.com';
V_conn utl_smtp.connection;
BEGIN
FOR Rec
IN ( SELECT Tbs.Tablespace_name,
Tot.Bytes / 1024 Total,
Tot.Bytes / 1024 - SUM (NVL (Fre.Bytes, 0)) / 1024 Used,
SUM (NVL (Fre.Bytes, 0)) / 1024 Free,
ROUND ( (1 - SUM (NVL (Fre.Bytes, 0)) / Tot.Bytes), 4)
* 100
Pct
FROM Dba_free_space Fre,
( SELECT Tablespace_name, SUM (Bytes) Bytes
FROM Dba_data_files
GROUP BY Tablespace_name) Tot,
Dba_tablespaces Tbs
WHERE Tbs.Tablespace_name = Tot.Tablespace_name
AND Fre.Tablespace_name(+) = Tot.Tablespace_name
GROUP BY Tbs.Tablespace_name, Tot.Bytes / 1024, Tot.Bytes
ORDER BY 5 DESC, 1)
LOOP
V_message :=
V_body
|| Rec.Tablespace_name
|| ' | '
|| TO_CHAR (Rec.Total)
|| ' | '
|| TO_CHAR (Rec.Used)
|| ' | '
|| TO_CHAR (Rec.Free)
|| ' | '
|| TO_CHAR (Rec.Pct)
|| UTL_TCP.Crlf;
END LOOP;
V_conn := utl_smtp.open_connection(V_host, 25);
utl_smtp.helo(V_conn, V_host);
utl_smtp.mail(V_conn, V_sender);
utl_smtp.rcpt(V_conn, V_recipient);
utl_smtp.open_data(V_conn);
utl_smtp.write_data(V_conn, 'From :' || V_sender || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'To :' || V_recipient || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'Cc :' || V_cc || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'Subject :'|| V_subject || UTL_TCP.Crlf ||
UTL_TCP.Crlf || V_message);
utl_smtp.close_data(V_conn);
utl_smtp.quit(V_conn);
END;
/
the total tablespace is 18 but when i executed the procedure im just got 1 in my email, did you see something wrong with that query?
regards,
Dio
|
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661581 is a reply to message #661566] |
Fri, 24 March 2017 04:27 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You've got two variables (v_message and v_body) where you only need one.
Your code doesn't populate v_body at any point.
Your code overwrites v_message with every iteration of the loop.
You need the loop to append the new data to the existing data with every iteration.
Just replace v_body with v_message in the executable section and remove it from the declare section.
|
|
|
|
|
Re: how to send tablespace mail alerts with PL/SQL [message #661817 is a reply to message #661816] |
Mon, 03 April 2017 21:56 |
|
diomahardhika
Messages: 18 Registered: March 2017
|
Junior Member |
|
|
im not sure but i guess not, here's my code :
create or replace PROCEDURE Sending_mail2 (
V_sender IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_recipient IN VARCHAR2 := 'dio.mahardhika@intra.sri-astra.com',
V_cc IN VARCHAR2 := 'nugraha.p.perdana@intra.sri-astra.com',
V_subject IN VARCHAR2 := 'Tablespace')
IS
V_body VARCHAR2 (32767);
Tablespace_name VARCHAR (60);
V_Message VARCHAR2(32767);
V_Host VARCHAR2(60) := 'intra.sri-astra.com';
V_conn utl_smtp.connection;
BEGIN
FOR Rec
IN ( SELECT Tbs.Tablespace_name,
Tot.Bytes / 1024 Total,
Tot.Bytes / 1024 - SUM (NVL (Fre.Bytes, 0)) / 1024 Used,
SUM (NVL (Fre.Bytes, 0)) / 1024 Free,
ROUND ( (1 - SUM (NVL (Fre.Bytes, 0)) / Tot.Bytes), 4)
* 100
Pct
FROM Dba_free_space Fre,
( SELECT Tablespace_name, SUM (Bytes) Bytes
FROM Dba_data_files
GROUP BY Tablespace_name) Tot,
Dba_tablespaces Tbs
WHERE Tbs.Tablespace_name = Tot.Tablespace_name
AND Fre.Tablespace_name(+) = Tot.Tablespace_name
GROUP BY Tbs.Tablespace_name, Tot.Bytes / 1024, Tot.Bytes
ORDER BY 5 DESC, 1)
LOOP
V_message :=
Rec.Tablespace_name
|| ' | '
|| TO_CHAR (Rec.Total)
|| ' | '
|| TO_CHAR (Rec.Used)
|| ' | '
|| TO_CHAR (Rec.Free)
|| ' | '
|| TO_CHAR (Rec.Pct)
|| UTL_TCP.Crlf;
END LOOP;
V_conn := utl_smtp.open_connection(V_host, 25);
utl_smtp.helo(V_conn, V_host);
utl_smtp.mail(V_conn, V_sender);
utl_smtp.rcpt(V_conn, V_recipient);
utl_smtp.open_data(V_conn);
utl_smtp.write_data(V_conn, 'From :' || V_sender || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'To :' || V_recipient || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'Cc :' || V_cc || UTL_TCP.Crlf);
utl_smtp.write_data(V_conn, 'Subject :'|| V_subject || UTL_TCP.Crlf ||
UTL_TCP.Crlf || V_message);
utl_smtp.close_data(V_conn);
utl_smtp.quit(V_conn);
END;
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:29:43 CDT 2024
|