Send Invalid Object to Mail


CREATE OR REPLACE PROCEDURE OC_SYS.SEND_MAIL (

msg_from varchar2 := 'INVALID_OBJECT_FOR_ANARDB@xxxxxxxx.com.az',

msg_to varchar2,

rcv_count number,

msg_subject varchar2 := 'E-Mail message from Anar_Godjaev_DBA') IS




c             utl_tcp.connection;

rc            integer;

Str_Inv       VARCHAR2(32000);




CURSOR Inv_List is

SELECT owner, object_type, object_name, status

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner, object_type, object_name;




BEGIN

Str_Inv := '            Owner              -                            Name                              -        Type   ' || CHR(10);




FOR vLST in Inv_List LOOP --

Str_Inv := Str_Inv ||

Lpad(vLST.owner, 30, ' ') || '  '  ||

Lpad(Substr(vLST.object_name, 1,60), 60, ' ') || '  '   ||

Lpad(Substr(vLST.object_type, 1,20), 20, ' ') || CHR(10);

if length(Str_Inv) > 31500 Then

Str_Inv := Str_Inv || ' Daha Var ...  ' ;

EXIT;

End if;

END LOOP;

IF (length(Str_Inv) > 120) THEN




FOR i IN 1..rcv_count loop




c := utl_tcp.open_connection('172.30.5.110', 25);       -- open the SMTP port 25 on local machine

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'HELO localhost');

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'RCPT TO: '||SPLIT(msg_to, i));

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'DATA');                 -- Start message body

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);

rc := utl_tcp.write_line(c, '');

rc := utl_tcp.write_line(c, 'Message was sent to: ' || msg_to);

rc := utl_tcp.write_line(c, '');

rc := utl_tcp.write_line(c, '');

rc := utl_tcp.write_line(c, Str_Inv);

rc := utl_tcp.write_line(c, '.');                    -- End of message body

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'QUIT');

dbms_output.put_line(utl_tcp.get_line(c, TRUE));

utl_tcp.close_connection(c);                         -- Close the connection




END LOOP;

END IF;

EXCEPTION WHEN OTHERS THEN

raise_application_error(

-20000, 'Unable to send e-mail message from pl/sql because of: '||

sqlerrm);

END;

/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: