Home » SQL & PL/SQL » SQL & PL/SQL » Help on stored procedure (Oracle 10.1)
Help on stored procedure [message #362505] Tue, 02 December 2008 08:42 Go to next message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
Hi,
I've this table AFM_USERS.

CREATE TABLE AFM_USERS
(
USER_PWD VARCHAR2(64 BYTE),
USER_NAME VARCHAR2(64 BYTE),
COUNTER 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.

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)


In attach I posted the table FGA_LOG$

I'd like to create a procedure like this:

Create or replace procedure update_counter
IS

cursor c1 is
select SESSIONID,DBUID,OSUID,OSHST,OBJ$SCHEMA,OBJ$NAME,POLICYNAME,SCN,NTIMESTAMP#,PROCESS#,STATEMENT,ENTRYID,LSQLBIND
from FGA_LOG$;

PROG NUMBER := 0;

begin
for a in c1 loop

IF I connect not correctly then

PROG := PROG + 1;

UPDATE AFM_SECURE.AFM_USERS
SET COUNTER = PROG
WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

END IF;

IF I connect correctly then

PROG := PROG - 1;

UPDATE AFM_SECURE.AFM_USERS
SET COUNTER = PROG
WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

END IF;

end loop;
commit;

EXCEPTION
WHEN OTHERS THEN
raise;
END;

But I don't know how can I found the correct connection.

When I connect correctly I get 2 records with OBJ$NAME = (AFM_USERS and AFM_MODS)
and 2 records with POLICY_NAME = (AFM_LOGIN and AFM_MODS)

When I connect not correctly I get ust one record with OBJ$NAME = AFM_USERS and POLICY_NAME = AFM_LOGIN

Have someone any idea??
Re: Help on stored procedure [message #362509 is a reply to message #362505] Tue, 02 December 2008 08:57 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
EXCEPTION
WHEN OTHERS THEN
raise;

What is the usage and purpose of this but just hiding where the error comes from?

Regards
Michel
Previous Topic: Partition truncate not releasing space?
Next Topic: Is it possible with a Trigger?
Goto Forum:
  


Current Time: Tue Dec 06 04:32:28 CST 2016

Total time taken to generate the page: 0.15853 seconds