Here is my code:

create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(1),
isLocked number(1)

I want to count attempt column and if 5 then isLocked become 1 from 0; How can I make query, view or procedure?

1 Answer 1


A couple of notes,

Rather than track only failures, it's generally a good idea to know when people sign in, too. There is probably already a table that handles authentication tokens, but there's no reason why failed_login_attempts couldn't contain both successful and unsuccessful authentication attempts.

By having isLocked on a secondary table you leave yourself open to a bunch of crazy hack "solutions" to fix data inconsistencies when an account should be marked as locked or not ... particularly for users who regularly lose their passwords. Logically, if an account is "locked", then that should go on the Account (or User) table so that you're not always hitting a table that will quickly have a lot of data to determine whether a login can take place.

For example:


Then ...


From here you can record all login attempts into LoginTXN (or whatever you decide to call it), mark the success field as 0 on failure, 1 on success (or N and Y), and go from there.

When checking to see how many login attempts have been made, you'll need to do something like:

  FROM LoginTXN txn INNER JOIN (SELECT z.account_id, MAX(z.id) as recent_success
                                  FROM LoginTXN z 
                                 WHERE z.account_id = 999
                                 GROUP BY z.account_id) sok ON txn.account_id = sok.account_id
                                                           AND txn.id > sok.recent_success

Note: This is pseudo-code. Don't simply copy/paste it.

Which will then give you a count. If you want to have conditions like "5 failed attempts in the last hour", thereby locking the account for — at most — 60 minutes, you could do that with a WHERE statement that examines the created_at value in LoginTXN.

Of course, if this is being put into a view or a stored procedure, then an is_locked field on Account may be wholly unnecessary as the authentication rule could be encapsulated completely within the view or stored procedure.

Hope this gives you a couple of pointers that will lead you to a more complete solution.

Friendly Aside: Try to stick with a consistent naming scheme when making tables. If you prefer to use words separated by underscores, like is_locked, then stick with that. If you prefer camel-case, like isLocked, then stick with that. You — and every other developer who works on this code in the future — will appreciate a predictable consistency. As you can probably see from my examples above, I tend to go with camel-cased tables, and underscored columns.

  • Thanks for your detailed reply. I am not saving success login attempts at all in this table but only failed attempts. (2) I agree isLocked is unnecessary and should be handled in view or procedure or query. (3) Thanks for Friendly Aside advice. Appreciated!!. I am looking for query, view or procedure please
    – MikeJ
    May 7, 2021 at 2:12
  • 2
    Regarding use of CamelCase names - unless you enclose them in double-quotes (in oracle) their names will really be all upper-case but oracle will treat references as case-INsensitive. If you really create them in CamelCase by enclosing them in double-quotes, you will always have to enclose any references in double-quotes and with the exact correct case That's why I advocate for underscore_separated. I also advocate for column names to be in the format adjective_noun. That eliminates accidentally trying to use a reserved word and is more self-documenting.
    – EdStevens
    May 7, 2021 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.