?
Solved

Is there any way to log into a table if the user exits out of i-explorer in oracle applications?

Posted on 2010-08-26
28
Medium Priority
?
656 Views
Last Modified: 2013-12-19
I have a form where I log the user in a sessions table when they log into the form . If a second user tries to log in I display the message saying Irradiation in progress, please wait until completed. And once they exit out I am clearing this sessions table. But accidently if the user exits the i-explorer there is no way for me to clear the sessions table. So the table is locked and the user cannot reenter the screen until the table is cleared. So i have to do in the background via toad to clear the table. If this happens in the midnight, as they irradiate all times, I have no way to do unless they call me home and I login to the system. Is there a way to control i-explorer or to log into another that this session was abruptly closed?
session.doc
0
Comment
Question by:anumoses
[X]
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
  • 15
  • 10
  • +2
28 Comments
 
LVL 17

Expert Comment

by:aiklamha
ID: 33532652
I believe this is the Oracle web form you're talking about? which platform did you use to develop the form?

0
 
LVL 6

Author Comment

by:anumoses
ID: 33532665
Oracle forms that is on Linux machine
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33533004
Can you change the application to time-out a user after say, 10 minutes of inactivity?  

Then you can create a database job that will delete any record in the sessions table older than say, 15 minutes.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33533147
When the Oracle Form closes due to IE being closed does the Oracle session associated with the database connection made by the form disappear?  I assume it does.

You could have a process running in background that monitors forms sessions by examining the  v$session table.

If it detects there is no session in v$session then it can assume the connection is lost and remove the row from your applications sessions table.

If you're able to modify the application, you could store the Oracle session identifier in your applications sessions table to make it easier to perform this check.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33533564
@Milleniumaire: seems to be a good one. But if I use v$session
SELECT s.sid,username,logon_time,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s
---------------
SID|USERNAME|LOGON_TIME|SERIAL#|OSUSER|PROGRAM
16|CARGULLO|8/26/2010 10:59:29 AM|25283|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
19|JILBERNA|8/26/2010 9:31:00 AM|17791|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
20|DIASTOLP|8/26/2010 9:34:59 AM|23509|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
22|DINHANLI|8/26/2010 8:59:16 AM|45302|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
23|GENEVA|8/25/2010 4:54:48 PM|21634|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
24|CRYSTAL|8/25/2010 3:48:56 PM|21980|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
25|VINEMRIC|8/26/2010 9:50:29 AM|39959|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
26|KEVKONRA|8/24/2010 9:44:23 AM|18258|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
28|ELADOTY|8/26/2010 8:25:01 AM|5585|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
29|MOBILEW|8/26/2010 9:35:41 AM|9999|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
31|ANNMCKAN|8/26/2010 8:26:37 AM|25071|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
34|ELIBUTLE|8/26/2010 8:00:02 AM|41945|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
35|RUSDEER|8/26/2010 10:05:41 AM|12389|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
36|NANLASH|8/26/2010 8:07:43 AM|18708|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
37|KARSCHWA|8/26/2010 7:56:48 AM|18281|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
38|SHASPIES|8/26/2010 9:19:02 AM|32570|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
42|DATAENT1|8/26/2010 6:13:55 AM|23616|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
53|VALSCOTT|8/26/2010 9:05:59 AM|18453|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
56|BOBHARTM|8/26/2010 10:02:20 AM|34440|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
61|CHAGROMA|8/26/2010 9:11:05 AM|30079|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
62|COMP01|8/26/2010 10:38:18 AM|27934|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
63|KATHENNI|8/26/2010 9:35:04 AM|20229|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
64|CHRCHISZ|8/26/2010 9:32:00 AM|17301|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
66|RUSDEER|8/26/2010 7:38:02 AM|11765|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
67|DATAENT1|8/26/2010 10:31:10 AM|11606|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
68|BARSTEIN|8/26/2010 8:05:53 AM|6264|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
70|MARMCKID|8/26/2010 11:06:53 AM|23974|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
75|JUDPESAV|8/26/2010 7:05:06 AM|18220|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
76|CLIFLEE|8/26/2010 8:17:16 AM|14736|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
77|FRABUCKL|8/26/2010 9:21:37 AM|6729|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
78|COUMERED|8/26/2010 9:29:48 AM|23537|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
82|DOUWINSO|8/26/2010 6:13:49 AM|15133|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
83|RAFCACIQ|8/26/2010 9:28:38 AM|26003|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
87|DEBTOPP|8/25/2010 10:27:47 AM|20699|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
90|JANGOLDM|8/26/2010 10:26:49 AM|10291|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)
91|JAMLAUER|8/26/2010 11:16:49 AM|19976|oracle|frmweb@hbc-maroon.heartlandbc.org (TNS V1-V3)

what do I haver to monitor?
what is Oracle session identifier  and is it in v$session?
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 33534039
i don't know oracle forms and i never used them. if you're using any html/javascript wrapper, may be you can write a function for body onunload

<body onunload="onBodyUnload()">

and in onBodyUnload you can make the ajax call to remove the session..
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33539753
The SID column in v$session identifies the session, so this could be stored in the application table and used to determine the Oracle session/connection still exists.

When killing sessions in Oracle (using alter system kill session...) it is necessary to supply both the SID and SERIAL#, both of which are available in V$SESSION.  This suggests that both are required to uniquely identify a session, but looking at my system, all sessions seem to have a unique SID.  I believe the SERIAL# value is used when a SID value is re-used i.e. a session is ended, and a new session starts, but is given the same SID.

Alternatively, your application can actually store information in V$SESSION by calling the dbms_application_info package.  This can set information such as a module name, an action or client information, all of which are stored in v$session.  See the following for details of this package:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_appinf.htm

It would therefore be possible for your form to use dbms_application_info.set_module to assign a module name to the current session.  This could then be used to identify relevant rows in v$session.  Of course, if you can have multiple instances of the same form running simultaneously this might not help and you'd be better off storing the SID & SERIAL# of the session.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33540924
CREATE or replace PROCEDURE add_session_user AS
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'add_session_user',
    action_name => 'insert into AURORA_RBC_IRRAD_SESSION');
  INSERT INTO AURORA_RBC_IRRAD_SESSION
    (SESSION_USER)
    VALUES (user);
  DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END;
------------------
If I create this procedure, can I call this in the form? and that way inserts value in my table the session_user
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 2000 total points
ID: 33541198
Sure, you could call this database procedure from a form, however it doesn't need to be created as a database procedure, you could include the procedure in the form.  This obviously depends on how many places it will be called from, but if only the form will use it why not put the code in the form.

To summarise what this code does:
- Updates the Module session information in v$session (for the current session) with the supplied value - literal 'add_session_user'.
- Insert a row into your applications own session table.
- Clears the Module session information in v$session (for the current session).

Also, I'm not sure if you're saying you would set the Module value to the literal 'add_session_user' , or if this would be replaced by a particular value.

Essentially, by setting and clearing the module information, this procedure does not give any benefit!

What you need to think about doing is:
In the PRE-FORM trigger
- Insert a row into the AURORA_RBC_IRRAD_SESSION table for the user
- Set the session module name using DBMS_APPLICATION_INFO.SET_MODULE to a specific value that uniquely identifies that forms session.

In the POST-FORM trigger
- Delete the row from the AURORA_RBC_IRRAD_SESSION table for the user
- Clear the session module name i.e. set it to null.

Then, whenever you compare the contents of v$session with AURORA_RBC_IRRAD_SESSION this will identify which forms sessions no longer exist.  You would expect rows in v$session with a value for module name to exist in the AURORA_RBC_IRRAD_SESSION table.  If they don't then the forms session has been stopped without clearing the data out of the AURORA_RBC_IRRAD_SESSION table.

As I explained in an early post, you could also store the session details (SID and SERIAL#) in the AURORA_RBC_IRRAD_SESSION table (if you can change this) to enable you to relate the rows in v$session with rows in AURORA_RBC_IRRAD_SESSION.  You could then try to find the rows in v$session using the SID and SERIAL# stored in AURORA_RBC_IRRAD_SESSION table.  If it finds it then you know the forms connection still exists, if it doesn't then the form must have closed without executing the POST-FORM trigger (which clears the data out of the AURORA_RBC_IRRAD_SESSION table.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33541232
The problem is in  my form I am not able to use v$session table. When I use this table, there is no compilation problem, but when I execute the form, the form does not start. And I dont know why? What is the mistake I am doing I am not able to understand.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33541451
Ok Module name is the name of my form.fmb ie au_rbc_irradiation_log, if I am taking it right.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33541461
In DBMS_APPLICATION_INFO.SET_MODULE  that I create, will I insert the value in V$SESSION . Can you clarify?
0
 
LVL 6

Author Comment

by:anumoses
ID: 33541675
I tested this
    DBMS_APPLICATION_INFO.SET_MODULE(
                 module_name => 'au_rbc_irradiation_log',
                 action_name => 'insert into AURORA_RBC_IRRAD_session');
Now in the V$SESSION I saw the data. It added the line

SELECT module,action
FROM   v$session
where program like 'frmweb@hbc-maroon.heartlandbc.org%'
and action is not null ;

MODULE|ACTION
au_rbc_irradiation_log|insert into au_irrad_session

On Exit
DBMS_APPLICATION_INFO.SET_MODULE(null,null);
There is no data in v$session.

Now I tested closing the i-explorer. The data in v$session is not there.
Perfect. But now I need to clear the AURORA_RBC_IRRAD_SESSION . When I use the v$session in my form to compare, the form does not open at all. So any solution for this?
If this part works, then I am all good to go.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33541726
Hi, the DMBS_APPLICATION_INFO package is actually updating specific columns in the session table (V$SESSION is a view on this table).  It knows which session row to update as Oracle always knows what the current session is.  In fact, you can get the current SID (session ID) yourself using:

select sys_context('USERENV','SID') from sys.dual;

You shouldn't need to access the v$session table directly in the form, sys_context allows you to get some session values as described here:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm
0
 
LVL 6

Author Comment

by:anumoses
ID: 33541761
Can I use this select sys_context('USERENV','SID') from sys.dual;
as is in the form? or I need a into clause?
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33541968
sys_context is an Oracle function, so you can call it from anywhere that functions can be used i.e. in a sql statement or a plsql block.

If used in a sql statement then yes it will need to be fetched INTO a variable.  To use it in a pl/sql block within forms simply do something like:

declare
  x number;
begin
  x := sys_context('USERENV','SID');
end;
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542009
 x := sys_context('USERENV','SID');
  message('x = '||x);pause;
I used this in the form
The form does not open. When I click on the main menu on the applications, the form come and shuts off.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33542130
Have you defined the variable x or is this setup as a non-database field?

What version of forms are you using?
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542148
x is a local variable that I defined.

Forms [32 Bit] Version 10.1.2.0.2 (Production)
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33542340
Although this isn't really related to the original question you asked, I've built a simple form called SYS_CONTEXT.fmb.

This is built in the same version of forms you are using and has a button, which calls SYS_CONTEXT when clicked and puts the result in a field.

This works for me when run through Oracle Forms Developer 10g.

Save the attachment, rename the file to SYS_CONTEXT.fmb and open and run it in Oracle Forms Developer.  (I had to change the extension as Experts Exchange only allows files with certain extensions to be attached).

If this form doesn't work for you then this is due to an issue with your installation/configuration.
SYS-CONTEXT.jpg
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542341
 x := sys_context('USERENV','SESSION_USER');
When I use the above, the form opens
So how can I find the above details to compare?
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33542424
The code is on the WHEN-BUTTON-PRESSED trigger on the the PUSH_BUTTON4 item in BLOCK3.  Here is is for reference:

declare
  x number;
begin
  x := sys_context('USERENV','SID');
  :result := x;
end;

Note: I didn't need to retrieve the value into a variable before assigning it to the field, I just did this for completeness.  The value could have been assigned directly to the result field using:

:result := sys_context('USERENV','SID');

which would have avoided the need to declare the variable x.  (Anyway, enough of the forms  training ;-)
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542553
Sorry for this
I get this error

Oracle/PLSQL: ORA-02003 Error
Error:
ORA-02003: invalid USERENV parameter
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542721
Had to use
  x := sys_context('USERENV','SESSIONID');

session.doc
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33542771
Hi, it looks like the 'SID' option is only available from Oracle 10g onwards, which is why you're probably getting the error.

SESSIONID isn't the same as SID, but I guess it could be used to compare against the AUDSID column in v$session.
0
 
LVL 6

Author Comment

by:anumoses
ID: 33542785
Yes. Correct. Now I am also inserting session id into my AURORA_RBC_IRRAD_SESSION table
Then I will compare. Once I am done I will close this
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33542851
Okay, sounds good.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 33557394
Thanks for the help
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

777 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