|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: getting last logon datetime [message #602810 is a reply to message #602806] |
Mon, 09 December 2013 05:03   |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
I doubt this suggestion a little.
I guess the OP is interested in the last-login-date of the end-user. I very much doubt that the end-user logs in to the database (via an application server).
Probably the application will use an application-specific user to login to oracle. The application then logs in the specific user.
DBA_AUDIT_SESSION supplies us with the logon information to the database (in this case probably from the application server).
However...I indeed have seen applications where the user more or less login to the database directly.
But since we are talking about a web application here, I'm quite sure that there is a web/application server in the middle.
|
|
|
|
|
|
| Re: getting last logon datetime [message #602812 is a reply to message #602811] |
Mon, 09 December 2013 05:17 |
 |
sss111ind
Messages: 636
Registered: April 2012
Location: India
|
Senior Member |
|
|
So now we changed the code on session basis like for entire session when the same session comes again and again it will not insert any new record hence returning the old max logon datetime .
PROCEDURE last_login_info(p_user_id IN VARCHAR2,
p_current_login IN VARCHAR2,
p_session_id IN VARCHAR2,
p_last_login OUT VARCHAR2)
AS
l_last_login DATE;
l_session_id VARCHAR2(30);
BEGIN
BEGIN
SELECT session_id,login_date
INTO l_session_id, l_last_login
FROM user_logon_info
WHERE login_date = (SELECT Max(login_date) FROM user_user_logon
WHERE user_id = p_user_id);
EXCEPTION
WHEN no_data_found THEN
l_last_login := NULL;
END;
IF l_last_login IS NULL THEN
INSERT INTO user_logon_info(user_id,login_date, session_id)
VALUES ( p_user_id,sysdate,p_session_id);
p_last_login := NULL;
ELSE
IF l_session_id=p_session_id THEN
p_last_login := To_char(l_last_login,'dd/mm/yyyy hh24:mi:ss');
ELSE
INSERT INTO user_logon_info (user_id, login_date, session_id)
VALUES ( p_user_id, sysdate, p_session_id);
p_last_login := To_char(l_last_login,'dd/mm/yyyy hh24:mi:ss');
END IF;
END IF;
COMMIT;
END last_login_info;
Please tell me if anything is wrong here.
[edit MC: Change "axis" to "user" at OP's request]
[Updated on: Mon, 09 December 2013 06:00] by Moderator Report message to a moderator |
|
|
|
| Re: getting last logon datetime [message #602815 is a reply to message #602812] |
Mon, 09 December 2013 05:26 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Apart from the commit shouldn't be there - the front end application should issue commit when it's done - it's hard to say.
You still haven't really explained what the procedure is supposed to do. We don't know if your sessions and users relate to oracle sessions and users or not. We don't know under what circumstances the procedure is called. We don't know the structure of the tables.
|
|
|
|
|
|
|
|
| Re: getting last logon datetime [message #602819 is a reply to message #602812] |
Mon, 09 December 2013 05:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Since the last logon datetime needs to be displayed in the frontend, the logic should be written in the frontend code. You do not require a stored procedure for this. You just need a SQL in your Java code which will just fetch the last record of session details for a particular user.
Your table needs to have following columns :
1. LOGIN_CD
2. SESSION_ID
3. DT_TM_STAMP
For every new session there will a new record inserted in this session details table for every user. Kind of audit. And before inserting your SQL would fetch the MAX(dt_tm_stamp) where LOGIN_CD=<current user>.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
