Home » SQL & PL/SQL » SQL & PL/SQL » Record frequency of App Module used (Oracle10g)
Record frequency of App Module used [message #355902] Tue, 28 October 2008 06:57 Go to next message
Messages: 6
Registered: January 2007
Junior Member
I have to write a procedure to calculate the frequency of any application module used per day.
For example, my corporate application comprises of HR, SALES etc and HR application is developed in OracleForms & SALES in OracleApex.
Both of these apps have several forms. I want to record the times, a particular form is used.
I guess if i can use Module column from v$session, that may work.
I wanted to know if someone has already developed this kind of program or if you guys have better idea, please do share.

Re: Record frequency of App Module used [message #355952 is a reply to message #355902] Tue, 28 October 2008 12:46 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have seen this many times. In most cases the following is done:

1) a table is created to hold the record keeping data
2) an autonomous transaction is written in PL/SQL to take in the necessary data and write it to the table
3) callouts to the plsql autonomous transaction are done in the proper entry points for whatever pieces of code are being monitored.

I have done this myself or worked with code that does this, of the following kinds:

oracle forms
plsql packages
oracle report writer
business objects
VB and .NET java applications

If you want to make sure that no application can run without doing this step, you can always require that applications grab a specific role from the database and you make this role assigned only via the record keeping application. This maybe is not an absolutely fool proof method, it does not for example force each module of a forms app to do the call, but at least it makes developers aware of what they have to do, when they first try to run and get kicked out. If you assign a role at the time you do the call for house keeping, then if the housekeeping call is not done, the role is not assigned. If the users can only do work if they have the specific role, then they get kicked out.

In general, you have to modify code to make this work. I am not familiary with any "short cut" methods using database supplied mechanisms. That does not mean they don't exist though.

Additionally, there is a cost to all this. Each call requires a commit. A commit involves some serious processing under the covers. If you have apps that make this call inside some loop where they should not be doing it, then you can overload your system with lots of commits which will kill your throughput.

Lastly, exacly how you code the call is application dependent. Sometimes there are techniques specific to a tool that make it easy, more often not so you have some work ahead of you.

Good luck, Kevin
Previous Topic: with and test
Next Topic: SQL for counting items in a list, from cropped fields
Goto Forum:

Current Time: Sat Dec 10 22:25:32 CST 2016

Total time taken to generate the page: 0.07563 seconds