Synonyms solved


Handling multiple user logins access to a common set of tables is as common in almost any Oracle applications. Typically there are four options to resolve the issue:

Having a private set quickly turns into a maintenance nightmare for large numbers of users; having public synonyms typically means more recursive work for the database to do when accessing objects and also problems when two object names clash in the same database; hard coding the application schema can mean users having to remember this when producing ad-hoc queries; similary the 'alter session' command can only be performed in application code.

Not any more ! Thanks to 8i, a much simpler solution now exists. The new LOGON trigger can be used to issue the 'alter session' command as soon as the user connects. Your trigger code merely needs:

declare
  c integer;
begin
  c := sys.dbms_sql.open_cursor;
  sys.dbms_sql.parse(c, 'alter session set current_schema = ...', sys.dbms_sql.native);
  sys.dbms_sql.close_cursor(c);
end;