scheduler job not returning correct value - always email even when not locked

this runs but emails even when the account is NOT locked - so what is wrong?

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'Monitor_AGENT_USER',
        job_type          => 'PLSQL_BLOCK',
        job_action        => q'[
DECLARE
    crlf      VARCHAR2(2) := CHR(13) || CHR(10);
    message_v VARCHAR2(32000);
BEGIN
    FOR i IN (SELECT user_id, account_status, lock_date
                FROM dba_users
               WHERE username = 'AGENT_USER' AND account_status = 'LOCKED')
    LOOP
        message_v :=
               message_v
            || i.user_id
            || 'is in '
            || i.account_status
            || 'status since '
            || TO_CHAR(i.lock_date, 'MM/DD/YYYY')
            || crlf;
    END LOOP;

    UTL_MAIL.send(
        sender       => 'bev@somedomain.com',
        recipients   => 'bev@somedomain.com',
        subject      => 'AGENT_USER Account is LOCKED',
        MESSAGE      => message_v
    );
END;
]',
        start_date        => TO_DATE('14:00', 'HH24:MI'),
        repeat_interval   => 'freq=minutely;interval=30',
        enabled           => TRUE,
        comments          => 'AGENT_USER monitor account lock'
    );
END;
/

Open in new window

LVL 1
bkreynolds48Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
First,  check if the block itself works, then we can check if it's a scheduler issue.

Turn on dbms_output capture
if sql*plus then

set serveroutput on

other tools will have other options, then run this
Do you get an email? What are the dbms output messages?

DECLARE
    crlf      VARCHAR2(2) := CHR(13) || CHR(10);
    message_v VARCHAR2(32000);
BEGIN
    FOR i IN (SELECT user_id, account_status, lock_date
                FROM dba_users
               WHERE username = 'AGENT_USER' AND account_status = 'LOCKED')
    LOOP
        message_v :=
               message_v
            || i.user_id
            || 'is in '
            || i.account_status
            || 'status since '
            || TO_CHAR(i.lock_date, 'MM/DD/YYYY')
            || crlf;
    END LOOP;

    IF message_v IS NOT NULL
    THEN
        DBMS_OUTPUT.put_line('Sending email');
        UTL_MAIL.send(
            sender       => 'bev@somedomain.com',
            recipients   => 'bev@somedomain.com',
            subject      => 'AGENT_USER Account is LOCKED',
            MESSAGE      => message_v
        );
        DBMS_OUTPUT.put_line('Email sent');
    ELSE
        DBMS_OUTPUT.put_line('No locked users found');
    END IF;
END;
0
 
slightwv (䄆 Netminder) Commented:
The utl_mail.send is outside any loop or IF check.  So, it will send a message no matter what.

If you only want it to send if a locked account is found then do something like:


...
if length(message_v) > 0 then
UTL_MAIL.send(
        sender       => 'bev@somedomain.com',
        recipients   => 'bev@somedomain.com',
        subject      => 'AGENT_USER Account is LOCKED',
        MESSAGE      => message_v
    );
end if;
...
0
 
sdstuberCommented:
small tweak to above ...

I suggest using


if message_v is not null  then

Open in new window


instead of

if length(message_v) > 0 then

Open in new window



if message_v isn't populated, the length won't be 0, the length will be NULL
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>the length will be NULL

Which will be less than 0.

Another thing that I don't like about the code.  There can only be one user with the username of 'AGENT_USER' so there is no need for the loop.

Just a single select and ignore the no_data_found exception will do the same thing.

If the select is successful then the message is set and the email sent.  If not, the exception handler kicks in and them email is not sent.

DECLARE
    crlf      VARCHAR2(2) := CHR(13) || CHR(10);
    message_v VARCHAR2(32000);
    v_user_id number;
    v_account_status varchar2(32);
   v_lock_date date;
   
BEGIN
    SELECT user_id, account_status, lock_date
    INTO v_user_id, v_account_status, v_lock_date
    FROM dba_users
    WHERE username = 'AGENT_USER' AND account_status = 'LOCKED';

    message_v :=
               message_v
            || v_user_id
            || 'is in '
            || v_account_status
            || 'status since '
            || TO_CHAR(v_lock_date, 'MM/DD/YYYY')
            || crlf;

   UTL_MAIL.send(
        sender       => 'bev@somedomain.com',
        recipients   => 'bev@somedomain.com',
        subject      => 'AGENT_USER Account is LOCKED',
        MESSAGE      => message_v
    );

    exception
         when no_data_found then null;
end;

Open in new window

0
 
sdstuberCommented:
>>the length will be NULL

Which will be less than 0



no,  not true

NULL is not equal to nor  not equal to nor greater than nor less than any value including another NULL.

Easy enough to test though....

DECLARE
    s VARCHAR2(20) := '';
BEGIN
    IF s IS NULL
    THEN
        DBMS_OUTPUT.put_line('s is null');
    ELSE
        DBMS_OUTPUT.put_line('s is not null');
    END IF;

    IF LENGTH(s) IS NULL
    THEN
        DBMS_OUTPUT.put_line('length(s) is null');
    ELSE
        DBMS_OUTPUT.put_line('length(s) is not null');
    END IF;

    IF LENGTH(s) = 0
    THEN
        DBMS_OUTPUT.put_line('length(s) = 0');
    ELSE
        DBMS_OUTPUT.put_line('length(s)  does not equal 0');
    END IF;

    IF LENGTH(s) != 0
    THEN
        DBMS_OUTPUT.put_line('length(s) != 0');
    ELSE
        DBMS_OUTPUT.put_line('length(s)  does NOT NOT equal 0  - double negative intentional');
    END IF;

    IF LENGTH(s) > 0
    THEN
        DBMS_OUTPUT.put_line('length(s) > 0');
    ELSE
        DBMS_OUTPUT.put_line('length(s) is not greater than 0');
    END IF;

    IF LENGTH(s) < 0
    THEN
        DBMS_OUTPUT.put_line('length(s) < 0');
    ELSE
        DBMS_OUTPUT.put_line('length(s) is not less than 0');
    END IF;

    IF LENGTH(s) = NULL
    THEN
        DBMS_OUTPUT.put_line('length(s) = NULL ');
    ELSE
        DBMS_OUTPUT.put_line('length(s) is not equal to NULL');
    END IF;

    IF LENGTH(s) != NULL
    THEN
        DBMS_OUTPUT.put_line('length(s) != NULL ');
    ELSE
        DBMS_OUTPUT.put_line('length(s) is NOT NOT equal to NULL   - double negative intentional');
    END IF;
END;

Open in new window



s is null
length(s) is null
length(s)  does not equal 0
length(s)  does NOT NOT equal 0  - double negative intentional
length(s) is not greater than 0
length(s) is not less than 0
length(s) is not equal to NULL
length(s) is NOT NOT equal to NULL   - double negative intentional

Open in new window




Having said that though, NULL >0 as a not true condition will work

It's just reads as a little weird to me since it's either TRUE or NULL, not TRUE or FALSE.
That's why I called the suggestion a "tweak" not a "correction"
0
 
bkreynolds48Author Commented:
slightwv I tried your tweak -  then I locked the account - did not receive an email
sdstuber - I tried yours and did not receive an email about the locked account either

can you help me figure out why?
0
 
slightwv (䄆 Netminder) Commented:
Another thing to look at is:  How did you lock the account?

There are several 'locked' states an account can be in.

You might look at changing the where clause to:
and  account_status like '%LOCKED%'

From the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5081.htm#REFRN23302

    OPEN
    EXPIRED
    EXPIRED(GRACE)
    LOCKED(TIMED)
    LOCKED
    EXPIRED & LOCKED(TIMED)
    EXPIRED(GRACE) & LOCKED(TIMED)
    EXPIRED & LOCKED
    EXPIRED(GRACE) & LOCKED
0
 
bkreynolds48Author Commented:
I put in the like statement
the output of dbms_output is

200is in LOCKEDstatus since 10/31/2013

so I am waiting to see if the job runs as well
is there anyway to format the output better?
0
 
bkreynolds48Author Commented:
also said
Sending email
Email sent
0
 
bkreynolds48Author Commented:
how do I put this in the job?  Just replace starting at DECLARE?
0
 
slightwv (䄆 Netminder) Commented:
>>is there anyway to format the output better?

You can format it however you want.  You are the one writing the code.

How do you want it?

>>Email sent

Then it looks like it ran.
0
 
slightwv (䄆 Netminder) Commented:
>>how do I put this in the job?  

sdstuber already gave you the job example in your previous question.  It is what you posted.

This question was the email beinf sent even if the account isn't locked. That was because of he logic you were using.  You weren't checking the output and sending the email no matter what.
0
 
sdstuberCommented:
drop the old job  

exec  dbms_scheduler.drop_job('Monitor_AGENT_USER');

Open in new window



then create new, using the same block as you have in the question
but, replace the job_action  parameter

that is, take the working block and put it inbetween the '[  and ]'

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'Monitor_AGENT_USER',
        job_type          => 'PLSQL_BLOCK',
        job_action        => q'[

---- put your working code here
 
]',
        start_date        => TO_DATE('14:00', 'HH24:MI'),
        repeat_interval   => 'freq=minutely;interval=30',
        enabled           => TRUE,
        comments          => 'AGENT_USER monitor account lock'
    );
END;
/

Open in new window

0
 
bkreynolds48Author Commented:
ok - I put the above in the job.
the job ran and emailed

200is in LOCKEDstatus since 10/31/2013

not sure what the "200" means

can I format the message better?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>not sure what the "200" means

It is the user_id you selected from dba_users.

>>can I format the message better?

Again, yes.  You are creating the string.  Change it however you want.

This is the line you need to change:

message_v :=
               message_v
            || i.user_id
            || 'is in '
            || i.account_status
            || 'status since '
            || TO_CHAR(i.lock_date, 'MM/DD/YYYY')
            || crlf;
0
 
bkreynolds48Author Commented:
Thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.