Hi George,
Tom Kytes book, Expert One on One Oracle. Has an excellent chapter on
Object Oreiented Features. His examples cover :
-Custom Data Types with Methods and Properties
-select * from pl_sql_function
-Present Object Releational view of relational data
-Create tables of different Types
What I would try in your case is using a GLOBAL TEMPORARY TABLE
and a LOGON Trigger..But you need 8i or newer.
I.E. Create a Logon Trigger that populates a GLOBAL TEMPORARY TABLE
That you can query and use throughout the users session.
When the user quits or exits the application the Table is cleared
automatically.
Here's a possible example...
CREATE GLOBAL TEMPORARY TABLE UserInfo(
ID Varchar2(20),
AccessLevel number(1),
Status number(1),
LoginTime date
);
--The default is the user only sees his sessions data you can also
make it so you can see all users data (see help)
--You could also make a custom type
create or replace trigger init_userobject
after logon on database
begin
insert into userinfo (ID,AccessLevel,Status,LoginTime)
values (user,1,0,sysdate);
end;
/
Then throught the application you can query the useinfo table.
This at least solves the problem of getting the object to exist during the
session. I'm sure you can wrap it in a more object oriented way
i.e. using a custom type and methods or even JAVA code...
But this is the way I would start with it..
Another common package to use is use the DBMS_APPLICATION_INFO
package which will register application info in the v$session table.
Have a look in the help and see if it will work for you.
I use it to register users that use my app when I query v$session
so that I can tell users in my app from other database users.
Hope That helps.