Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem sending emails

562372Oct 27 2008

Here’s a puzzler for anyone who is interested. I have Oracle XE and Apex 3.1 installed on my PC at work, (the PC is part of a local network which includes a mail server), recently there have been problems sending e-mails from this PC. Currently there is a situation where: if UTL_SMTP is used, emailing works fine but if I try using HTMLDB_MAIL or APEX_MAIL, the following exception is raised:

ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for phil.robinson@royalfree.nhs.uk

The Oracle user sending/trying to send the e-mails has execute access on all three of the above-named packages. It’s all very frustrating because APEX_MAIL used to work fine and only stopped working after I had to re-install Oracle and APEX. Results are the same if the procedures are run via SQL*Server or pressing a button on an APEX application. All help gratefully appreciated.

regards, Phil

Source code follows................

CREATE OR REPLACE PROCEDURE PROC_MAIL1 AS

c utl_smtp.connection;
l_from varchar2(50) := 'yellow@huts.co.uk';
l_to varchar2(50) := 'phil.robinson@royalfree.nhs.uk';
l_subject varchar2(50) := 'Fix this email problem 3';
l_body varchar2(50) := 'There have been problems sending emails from this PC;
l_mail_server varchar2(50) := '123.123.123.123';
l_module EXCEPTIONS.module_name%TYPE;
l_section EXCEPTIONS.module_section%TYPE;
l_exception EXCEPTIONS.exception_message%TYPE;

BEGIN

c := utl_smtp.open_connection(l_mail_server, 25); -- SMTP on port 25
utl_smtp.helo(c, l_mail_server);

utl_smtp.mail(c, l_from);

utl_smtp.rcpt(c, l_to);

utl_smtp.data(c,'From: '||l_from || utl_tcp.crlf ||
'To: ' || l_to || utl_tcp.crlf ||
'Subject: ' || l_subject ||
utl_tcp.crlf || l_body);

utl_smtp.quit(c);
DBMS_OUTPUT.PUT_LINE('finished OK');

EXCEPTION
WHEN OTHERS THEN
l_module := 'PROC_SEND_LATEST_POSTINGS';
l_section := 'EMAIL';
l_exception := SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_exception);
INSERT INTO EXCEPTIONS (id, module_name, module_section, exception_message)
VALUES (seq_exceptions.NEXTVAL, l_module, l_section, l_exception);
COMMIT;

END PROC_MAIL1;
/

CREATE OR REPLACE PROCEDURE PROC_MAIL2 AS

l_body CLOB;
l_module EXCEPTIONS.module_name%TYPE;
l_section EXCEPTIONS.module_section%TYPE;
l_exception EXCEPTIONS.exception_message%TYPE;

BEGIN

l_body := 'Problem sending emails from this PC'||utl_tcp.crlf||utl_tcp.crlf;

l_body := l_body ||'See how we can fix this at 16:35'||utl_tcp.crlf||utl_tcp.crlf;

l_body := l_body || '++++ End of message ++++';

HTMLDB_MAIL.send(
p_to => 'phil.robinson@royalfree.nhs.uk',
p_from => 'yellow@huts.co.uk',
p_body => l_body,
p_subj => 'Fix this email problem at 16:35');

HTMLDB_MAIL.PUSH_QUEUE(123.123.123.123', '25');

EXCEPTION
WHEN OTHERS THEN
l_module := 'PROC_SEND_LATEST_POSTINGS';
l_section := 'EMAIL';
l_exception := SQLERRM;
INSERT INTO EXCEPTIONS (id, module_name, module_section, exception_message)
VALUES (seq_exceptions.NEXTVAL, l_module, l_section, l_exception);
COMMIT;
END PROC_MAIL2;
/

Hoek

Hi Wateenmooiedag,

it's cloudy over here btw ;)

Have you tried using LAG:

MHO%xe> select t
  2  ,      userid
  3  ,      case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 ) ) >= 1
  4              then 1
  5              else 0
  6         end cnt
  7  from   clickstream;

T                       USERID        CNT
------------------- ---------- ----------
05-09-2009 10:00:00          2          0
05-09-2009 10:00:24          2          0
05-09-2009 10:01:23          2          0
05-09-2009 10:02:40          2          1
05-09-2009 00:58:24          7          0
05-09-2009 02:30:33          7          1

6 rijen zijn geselecteerd.

( It reminded me of this link:http://asktom.oracle.com/tkyte/Misc/DateDiff.html )

486393

It is not exactly what I had in mind.

When I add two new rows:

insert into clickstream values (to_date('5-9-2009 10:02:50','dd-mm-yyyy hh24:mi:ss') , 2);
insert into clickstream values (to_date('5-9-2009 10:05:40','dd-mm-yyyy hh24:mi:ss') , 2);

It should return:

T                       USERID        CNT
------------------- ---------- ----------
05-09-2009 10:00:00          2          0
05-09-2009 10:00:24          2          0
05-09-2009 10:01:23          2          0
05-09-2009 10:02:40          2          1
05-09-2009 10:02:50          2          1
05-09-2009 10:05:40          2          2
05-09-2009 00:58:24          7          0
05-09-2009 02:30:33          7          1

Because user 2 started a new session on 10:02:40 and another one on 10:05:40 .

By the way it's cloudy here too but that should not suprise you! :) (wateenmooiedag means what a beautiful day in Dutch)

Edited by: wateenmooiedag on Sep 6, 2009 3:33 AM

Hoek

Aha, OK, that calls for some adjustment , indeed.

One little thing:
Shouldn't

insert into clickstream values (to_date('5-9-2009 10:02:40','dd-mm-yyyy hh24:mi:ss') , 2);

(from your extra inserts)
be

insert into clickstream values (to_date('5-9-2009 10:02:50','dd-mm-yyyy hh24:mi:ss') , 2);

when looking at your desired output?

And what exactly makes the requirement for CNT = 2 on 10:05:40?
And how does 10:02:50 being 1 relate to that (since it differs only 10 secs from the previous t)?
Because I interpreted the requirement like:
"For at least a difference of 60 seconds (one minute) between the current and previous t we will set CNT to 1."
But that apparently isn't enough.

486393

It should indeed be: insert into clickstream values (to_date('5-9-2009 10:02:_50_'...

Every session should have a unique number (SESSIONID) assigned (unique per userid).

I should have renamed column CNT to SESSIONID.

Hoek

If the number has to be unique but it has no further meaning or purpose then maybe using row_number() is OK here:

MHO%xe> select t
  2  ,      userid
  3  ,      nvl(last_value(nullif(sessionid, 0) ignore nulls) over ( partition by userid order by userid, t),0) sessionid
  4  from ( select t
  5         ,      userid 
  6  --       ,      trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 ) ) minutes
  7         ,      case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1
  8                     then row_number() over ( partition by userid order by userid, t)
  9                     else 0
 10                end sessionid
 11         from   clickstream
 12       );

T                       USERID  SESSIONID
------------------- ---------- ----------
05-09-2009 10:00:00          2          0
05-09-2009 10:00:24          2          0
05-09-2009 10:01:23          2          0
05-09-2009 10:02:40          2          4
05-09-2009 10:02:50          2          4
05-09-2009 10:05:40          2          6
05-09-2009 00:58:24          7          0
05-09-2009 02:30:33          7          2

8 rijen zijn geselecteerd.

I've made a few assumptions here regarding setting sessionid to 4 on 10:02:50:
Once a session is identified, then keep that session id for the next records, regardless of the difference in minutes/seconds until the difference is at least a minute again.

1 person found this helpful

486393

That looks very good!! I added a dense_rank() to improve the sessionid:

select t
,      userid
,      -1 + (dense_rank() over (partition by userid order by sessionid)) sessionid
from
(
select t
,      userid
,      nvl(last_value(nullif(sessionid, 0) ignore nulls) over 
       ( partition by userid order by userid, t),0) sessionid
from ( select t
     ,      userid 
     ,      case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1
                    then row_number() over ( partition by userid order by userid, t)
                    else 0
            end sessionid
     from   clickstream
)
)
/

You can read here more: http://www.asterdata.com/resources/downloads/whitepapers/sqlmr.pdf , chapter 1.2 .

Edited by: wateenmooiedag on Sep 6, 2009 5:27 AM

Hoek

Yes ofcourse, dense_rank() tops it off nicely, didn't think of that one.

Interesting link by the way, thanks Tuinstoel.
It states SQL needs 'an expensive self-join' but by using analytics we don't need a self-join at all.

MHO%xe> select t
  2  ,      userid
  3  ,      -1 + (dense_rank() over (partition by userid order by sessionid)) sessionid
  4  from ( select t
  5         ,      userid
  6         ,      nvl(last_value(nullif(sessionid, 0) ignore nulls) over 
  7                ( partition by userid order by userid, t),0) sessionid
  8         from ( select t
  9                ,      userid 
 10                ,      case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1
 11                               then dense_rank() over ( partition by userid order by userid, t)
 12                               else 0
 13                       end sessionid
 14                from   clickstream
 15              )
 16       );
Verstreken: 00:00:00.25

Uitvoeringspan
----------------------------------------------------------
Plan hash value: 1413192182

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     8 |   280 |     5  (40)| 00:00:01 |
|   1 |  WINDOW SORT           |             |     8 |   280 |     5  (40)| 00:00:01 |
|   2 |   VIEW                 |             |     8 |   280 |     4  (25)| 00:00:01 |
|   3 |    WINDOW BUFFER       |             |     8 |   280 |     4  (25)| 00:00:01 |
|   4 |     VIEW               |             |     8 |   280 |     4  (25)| 00:00:01 |
|   5 |      WINDOW SORT       |             |     8 |   176 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| CLICKSTREAM |     8 |   176 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

It would be very interesting to see how this query would look like when using the MODEL-clause or some XML function, by the way...
I think this requirement can be met using less code that way.

hopes a few other well-known users will drop in here ;)

MichaelS

Answer

Looks to me you can do a bit simpler:

SQL>  select userid, t, nvl (sum (s) over (partition by userid order by t), 0) sessionid
from (select userid, 
             t, 
             case when t - lag(t) over (partition by userid order by t) > 1 / (24 * 60) then 1 end s
      from clickstream
      order by userid, t)
/
    USERID T                      SESSIONID
---------- --------------------- ----------
         2 05.09.2009 10:00:00            0
         2 05.09.2009 10:00:24            0
         2 05.09.2009 10:01:23            0
         2 05.09.2009 10:02:40            1
         2 05.09.2009 10:02:50            1
         2 05.09.2009 10:05:40            2
         7 05.09.2009 00:58:24            0
         7 05.09.2009 02:30:33            1

8 rows selected.

Marked as Answer by 486393 · Sep 27 2020

Hoek

Nice and very elegant, Michael!
It looks so easy, but it just never came to my mind...
memorizing the '> 1 / (24 * 60)' - part

Aketi Jyuuzou

I like using interVal ;-)

create table streamT(USERID,TimeVal) as
select 2,to_date('2009-09-05 10:00:00','YYYY/MM/DD HH24:MI:SS') from dual union
select 2,to_date('2009-09-05 10:00:24','YYYY/MM/DD HH24:MI:SS') from dual union
select 2,to_date('2009-09-05 10:01:23','YYYY/MM/DD HH24:MI:SS') from dual union
select 2,to_date('2009-09-05 10:02:40','YYYY/MM/DD HH24:MI:SS') from dual union
select 2,to_date('2009-09-05 10:02:50','YYYY/MM/DD HH24:MI:SS') from dual union
select 2,to_date('2009-09-05 10:05:40','YYYY/MM/DD HH24:MI:SS') from dual union
select 7,to_date('2009-09-05 00:58:24','YYYY/MM/DD HH24:MI:SS') from dual union
select 7,to_date('2009-09-05 02:30:33','YYYY/MM/DD HH24:MI:SS') from dual;

select USERID,to_char(TimeVal,'yyyy-mm-dd hh24:mi:ss') as TimeVal,
count(willCnt) over(partition by USERID order by TimeVal) as SESSIONID
from (select USERID,TimeVal,
      case when TimeVal-interVal '1' minute >=
                Lag(TimeVal) over(partition by USERID order by TimeVal)
           then 1 end as willCnt
        from streamT)
order by USERID,TimeVal;

 USERID  TimeVal              SESSIONID
-------  -------------------  ---------
      2  2009-09-05 10:00:00          0
      2  2009-09-05 10:00:24          0
      2  2009-09-05 10:01:23          0
      2  2009-09-05 10:02:40          1
      2  2009-09-05 10:02:50          1
      2  2009-09-05 10:05:40          2
      7  2009-09-05 00:58:24          0
      7  2009-09-05 02:30:33          1

1 person found this helpful

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 24 2008

Added on Oct 27 2008

#email

0 comments

381 views

Oracle Database Express Edition (XE)

Problem sending emails

Comments

Post Details