Can I make a procedure run every time someone logs on to the system ?

The Oracle (tm) Users' Co-Operative FAQ

Can I make a procedure run every time someone logs on to the system ?

Before 8i came along, performing an action when someone logged in (and doing it within the context of their session) meant explicitly architecting the application to do so. From 8.1.5, a new triggers have been created to handle this for you. They can be used to run any appropriate initialisation routines such as altering the CURRENT_SCHEMA or turning on/off debugging and/or tracing options.

An example of a logon trigger set to fire only when SCOTT connects is:

create or replace trigger INIT_APPLICATION
after logon on SCOTT.schema
begin
 my_app.set_global_variables;
end;
/

The "on database" construct can be used if you want to run the trigger when anyone connects to the database. If you are creating the same trigger for many users, it would be more efficient to create a single on-database trigger and test for the appropriate usernames, rather than having hundreds/thousands of individual schema level triggers.

Note that if the trigger fails, the user is still permitted to connect to the database. An error will be recorded in a trace file, for example:

create or replace trigger INIT_APPLICATION
after logon on SCOTT.schema
begin
 raise_application_error(-20000,'My custom error');
end;
/

would generate the following entries in a trace file

Skipped error 604 during the execution of SCOTT.INIT_APPLICATION
*** 2001-08-10 09:22:14.714
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: My custom error
ORA-06512: at line 2

Further reading: An example on how to avoid public or too many private synonyms here