Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Tag an Oracle SQL*XL session (Read 3916 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Tag an Oracle SQL*XL session
24.01.07 at 11:17:49
 
Tag an Oracle SQL*XL session
 
Alex from the USA sent us the following question.
 
Question:
Currently we have problems monitoring Excel sessions in Oracle. Oracle sees an Excel application as a generic "ODBC program", thus making it impossible to distinguish between spreadsheets with different functions. We would like to know if it's possible with SQL*XL to tag OO4O or ODBC connection properties? In other words, we'd like Oracle to sees a particular spreadsheet as, say, "Daily Profit and Loss", another one as "Current Positions", etc. Is it possible to do?  
 
Answer:
Session information in Oracle can be viewed through the system view v$session:"
select * from v$session
 
You cannot distinquish one SQL*XL session from another apart from the username, OSUser, Machine and terminal. If this information does not provide the full picture, consider using the DBMS_APPLICATION_INFO package. You can set further columns in the v$session table: module, action and client_info
 
These fields can be set with any character data. To show you how this is done consider the following example:

begin
  dbms_application_info.set_module(module_name => 'SQL*XL',  
                      action_name => 'Viewing Daily Profit and Loss');
  dbms_application_info.set_client_info('Tom @ finance');
end;

 
You can run this anonymous PL/SQL block in Excel through the normal SLQ*XL SQL dialog. What you could do is to record the running of this PL/SQL in a macro. Code in the workbook_open of your spreadsheets a call to this macro. Just put the name of the spreadsheet as action name and you will be able to see in v$session who is looking at which spreadsheet.
 

SQL> select module, action, client_info from v$session
  2  /
 
MODULE    ACTION                         CLIENT_INFO
------    ------                         -----------
SQL*XL    Viewing Daily Profit and Loss  Tom @ finance

Back to top
 
« Last Edit: 24.01.07 at 21:19:06 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Tag an Oracle SQL*XL session
Reply #1 - 24.01.07 at 22:23:09
 
I have implemented this tagging now in the mainstream SQL*XL application. When you connect SQL*XL to a database it will tag the session with module name SQL*XL. This is implemented in SQL*XL 4.1.10
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1