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 Go to next message
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 #661282 is a reply to message #661280] Mon, 13 March 2017 21:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which line has the error?

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.


Re: how to send tablespace mail alerts with PL/SQL [message #661285 is a reply to message #661282] Mon, 13 March 2017 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>into tablespace_name, total, used, free, pct;
Problem above with semicolon at right end of line.
Re: how to send tablespace mail alerts with PL/SQL [message #661286 is a reply to message #661285] Mon, 13 March 2017 22:14 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
thank you black swan.
the error was appear when you execute that code, example:
begin
sending_mail2;
end;


Re: how to send tablespace mail alerts with PL/SQL [message #661291 is a reply to message #661286] Tue, 14 March 2017 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Re: how to send tablespace mail alerts with PL/SQL [message #661292 is a reply to message #661291] Tue, 14 March 2017 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #661293 is a reply to message #661292] Tue, 14 March 2017 01:30 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
so? do you have any idea michel?
Re: how to send tablespace mail alerts with PL/SQL [message #661295 is a reply to message #661293] Tue, 14 March 2017 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #661297 is a reply to message #661295] Tue, 14 March 2017 01:45 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
im still noob in the oracle hehe my bad.
so what I supposed to do?
using cursor or for clause?
Re: how to send tablespace mail alerts with PL/SQL [message #661300 is a reply to message #661297] Tue, 14 March 2017 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
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 #661304 is a reply to message #661303] Tue, 14 March 2017 01:58 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
v_message error because it was inside the procedure, so how can i figure it out?
am i can using v_body?
Re: how to send tablespace mail alerts with PL/SQL [message #661305 is a reply to message #661304] Tue, 14 March 2017 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ FORMAT your posts!
2/ Yes.
Re: how to send tablespace mail alerts with PL/SQL [message #661317 is a reply to message #661305] Tue, 14 March 2017 08:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #661565 is a reply to message #661564] Thu, 23 March 2017 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>did you see something wrong with that query?
yes every time thru the LOOP the V_MESSAGE variable is set, not used, & then over written by the next iteration
>V_message :=
Re: how to send tablespace mail alerts with PL/SQL [message #661566 is a reply to message #661565] Fri, 24 March 2017 01:09 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
so it wont be able to show all of it? Sad im so confused hehe
Re: how to send tablespace mail alerts with PL/SQL [message #661581 is a reply to message #661566] Fri, 24 March 2017 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #661814 is a reply to message #661581] Mon, 03 April 2017 20:21 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
when i remove v_message and v_body the script cant be to compile, there is an error.
im so confused how to got all of my tablespace, here's the result. im just got 1 from 18 tablespace name,
what should i do to get all of tablespace name? please help.
  • Attachment: as.PNG
    (Size: 5.15KB, Downloaded 737 times)
Re: how to send tablespace mail alerts with PL/SQL [message #661816 is a reply to message #661814] Mon, 03 April 2017 21:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
we can't debug code we can NOT see.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Do you have SELECT the returns all the desired data for the email message?
Re: how to send tablespace mail alerts with PL/SQL [message #661817 is a reply to message #661816] Mon, 03 April 2017 21:56 Go to previous messageGo to next message
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;
Re: how to send tablespace mail alerts with PL/SQL [message #661818 is a reply to message #661817] Mon, 03 April 2017 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
change it to be like below

V_message := V_message ||
Rec.Tablespace_name
|| ' | '
|| TO_CHAR (Rec.Total)
|| ' | '
|| TO_CHAR (Rec.Used)
|| ' | '
|| TO_CHAR (Rec.Free)
|| ' | '
|| TO_CHAR (Rec.Pct)
|| UTL_TCP.Crlf;
Re: how to send tablespace mail alerts with PL/SQL [message #661819 is a reply to message #661818] Mon, 03 April 2017 22:13 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
alright i'll try it first
Re: how to send tablespace mail alerts with PL/SQL [message #661820 is a reply to message #661818] Mon, 03 April 2017 22:17 Go to previous messageGo to next message
diomahardhika
Messages: 18
Registered: March 2017
Junior Member
it works, thank you so much. it means a lot to me hehe, im very appreciate for your help.
and this the result
  • Attachment: tspace.PNG
    (Size: 28.64KB, Downloaded 857 times)
Re: how to send tablespace mail alerts with PL/SQL [message #661855 is a reply to message #661820] Tue, 04 April 2017 03:56 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I did say that you should replace v_body with v_message, not remove both.
Previous Topic: How can I convert a SQL command to MySql
Next Topic: Need a help with SQL
Goto Forum:
  


Current Time: Fri Mar 29 04:42:54 CDT 2024