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
623 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
  • 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 76

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
 
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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now