Hi,
I've this table AFM_USERS.

CREATE TABLE AFM_USERS
(
USER_PWD VARCHAR2(64 BYTE),
USER_NAME VARCHAR2(64 BYTE),
BAD_LOGIN NUMBER DEFAULT 0
)

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............0
JOHN..............JOHN$.............0
SMITH.............SMITH$............0
KOSTER............KOSTER$...........0
........................................
........................................

I've my application that connect Oracle via ODBC.

When I connect, for example, with USER_NAME=ADAMS PASSWORD=ADAMS$ into sys.fga_log$ table I get 2 records:

OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.......TIMESTAMP.............. LSQLTEXT...............................................................LSQLBIND
191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
191.164.2.34\Sam...191.164.2.34.....AFM........AFM_MODS....AFM_MODS.....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................


When I connect, for example, with USER_NAME=SMITH PASSWORD=SMITH$ into sys.fga_log$ table I get others 2 records:

OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

When I connect, for example, with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) into sys.fga_log$ table I get just one record:

OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH

When I connect, for example, with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) into sys.fga_log$ table I get just one record:

OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH

191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN...01/12/2008 7:17:04,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):KOSTER

Now I'd like to create a stored procedure on sys.fga_log$ table that increase or decrease of 1 the COUNTER column of table AFM_USERS with these conditions:

if I connect not correctly (user o password incorrect) I increase COUNTER of 1 (+1)
if I connect correctly (user and password correct) I decrease COUNTER of 1 (-1)

In my case I'd like to get like this:

When I connect with USER_NAME=ADAMS PASSWORD=ADAMS$ I'd like to get:

execute my_stored_procedure;

select *
from AFM_USERS;

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............-1 -- (-1)
JOHN..............JOHN$.............0
SMITH.............SMITH$............0
KOSTER............KOSTER$...........0
........................................
........................................

When I connect with USER_NAME=SMITH PASSWORD=SMITH$ I'd like to get:

execute my_stored_procedure;

select *
from AFM_USERS;

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............-1
JOHN..............JOHN$.............0
SMITH.............SMITH$............-1 -- (-1)
KOSTER............KOSTER$...........0

When I connect with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) I'd like to get:

execute my_stored_procedure;

select *
from AFM_USERS;

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............-1
JOHN..............JOHN$.............0
SMITH.............SMITH$............0 -- (+1)
KOSTER............KOSTER$...........0


When I connect with USER_NAME=SMITH PASSWORD=BBBBB(Bad Password) I'd like to get:

execute my_stored_procedure;

select *
from AFM_USERS;

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............-1
JOHN..............JOHN$.............0
SMITH.............SMITH$............1 -- (+1)
KOSTER............KOSTER$...........0

When I connect with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) I'd like to get:

execute my_stored_procedure;

select *
from AFM_USERS;

USER_NAME.......USER_PSWD........COUNTER
ADAMS.............ADAMS$............-1
JOHN..............JOHN$.............0
SMITH.............SMITH$............1
KOSTER............KOSTER$...........1 -- (+1)


How can I create my stored procedure?


Thanks in advance.