Using UTL_SMTP to send email alerts and Resumable Operations

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := ‘Gavin’;
mailhost VARCHAR2(30) := ‘harpoon.hq.emirates.com’; — local database host

BEGIN
c := utl_smtp.open_connection(mailhost, 25); — SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,’From: Oracle Database’ || utl_tcp.crlf ||
‘To: ‘ || msg_to || utl_tcp.crlf ||
‘Subject: ‘ || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(‘ Invalid Operation in Mail attempt
using UTL_SMTP.’);
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(‘ Temporary e-mail issue – try again’);
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(‘ Permanent Error Encountered.’);
END;
/
SQL> create or replace trigger send_message_trg
  2  after suspend on database
  3  declare
  4  begin
  5  send_mail(‘gavin.soorma@emirates.com’,’RESUMABLE ALERT’,’Check the alert log for suspended sessions!’);
  6   commit;
  7  end;
  8  /

Trigger created.
SQL> create table mytest
  2  tablespace users
  3  storage (initial 10k next 10k maxextents 5)
  4   as select * from dba_objects
  5  ;

Table created.

SQL> insert into mytest
  2  select * from mytest;

45586 rows created.

SQL> /

91172 rows created.
INSERT HANGS HERE >>>>>>>>
 select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
  SQL_TEXT, ERROR_NUMBER, ERROR_MSG
   from dba_resumable;

   USER_ID SESSION_ID STATUS    START_TIME           RESUME_TIME
———- ———- ——— ——————– ——————–
SQL_TEXT
——————————————————————————–
ERROR_NUMBER
————
ERROR_MSG
——————————————————————————–
         0        148 SUSPENDED 07/20/07 12:24:15
insert into mytest select * from mytest
        1653
ORA-01653: unable to extend table SYS.MYTEST by 128 in tablespace USERS

 
  COUNT(*) EVENT
———- —————————————————————-
         1 statement suspended, wait error to be cleared

SQL> alter database datafile ‘/hqlinuxbl200db01/ORACLE/onwp/users01.dbf’
  2  resize 100m;

Database altered.
INSERT CONTINUES >>>>>>>>>>>>>>>>>>>>>>

SQL> /

364688 rows created.

SQL>

SQL>  select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
  SQL_TEXT, ERROR_NUMBER, ERROR_MSG
   from dba_resumable;   2    3

   USER_ID SESSION_ID STATUS    START_TIME           RESUME_TIME
———- ———- ——— ——————– ——————–
SQL_TEXT
——————————————————————————–
ERROR_NUMBER
————
ERROR_MSG
——————————————————————————–
         0        148 NORMAL    07/20/07 12:24:15    07/20/07 12:39:20

The following two tabs change content below.

• Bio
• Latest Posts

Gavin Soorma

Latest posts by Gavin Soorma (see all)