Solved

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

Posted on 2013-10-29
17
418 Views
Last Modified: 2013-11-02
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

0
Comment
Question by:bkreynolds48
  • 6
  • 6
  • 4
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39609940
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39609989
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39610418
>>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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39610435
>>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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614275
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39614507
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39614534
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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614682
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614685
also said
Sending email
Email sent
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614689
how do I put this in the job?  Just replace starting at DECLARE?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39614693
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39614700
>>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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39614727
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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614741
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39614748
>>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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39614975
Thanks so much
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now