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

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
LVL 6
anumosesAsked:
Who is Participating?
 
MilleniumaireCommented:
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
 
Muhammad KhanManager, ITCommented:
I believe this is the Oracle web form you're talking about? which platform did you use to develop the form?

0
 
anumosesAuthor Commented:
Oracle forms that is on Linux machine
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
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
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
@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
 
ram_0218Commented:
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
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
Ok Module name is the name of my form.fmb ie au_rbc_irradiation_log, if I am taking it right.
0
 
anumosesAuthor Commented:
In DBMS_APPLICATION_INFO.SET_MODULE  that I create, will I insert the value in V$SESSION . Can you clarify?
0
 
anumosesAuthor Commented:
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
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
Can I use this select sys_context('USERENV','SID') from sys.dual;
as is in the form? or I need a into clause?
0
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
 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
 
MilleniumaireCommented:
Have you defined the variable x or is this setup as a non-database field?

What version of forms are you using?
0
 
anumosesAuthor Commented:
x is a local variable that I defined.

Forms [32 Bit] Version 10.1.2.0.2 (Production)
0
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
 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
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
Sorry for this
I get this error

Oracle/PLSQL: ORA-02003 Error
Error:
ORA-02003: invalid USERENV parameter
0
 
anumosesAuthor Commented:
Had to use
  x := sys_context('USERENV','SESSIONID');

session.doc
0
 
MilleniumaireCommented:
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
 
anumosesAuthor Commented:
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
 
MilleniumaireCommented:
Okay, sounds good.
0
 
anumosesAuthor Commented:
Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.