Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-10-29
17
428 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 74

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

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 74

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
 
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 74

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

790 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