Changing default schema at logon

I have one user with no quotas. The user's single purpose is to use a specific schema. While testing as that user, it becomes quite cumbersome to type in the schema name all the time.

I created a trigger to change the current schema at logon time.

The user is "MOO" and the schema to be used is "COW".

CREATE OR REPLACE TRIGGER Moo.Change_Default_Schema_To_Cow AFTER LOGON ON Moo.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET Current_Schema = Cow'; END Change_Default_Schema_To_Cow;
/

This seems to work. That is, when Moo logs on he can do a DESC on an object in Cow without the schema qualifier. When logging on as another user, the schema is not changed. So, I'll assume that I got this part correct.

This all works when I log in via SQL*Plus. However, when I log in via some other GUI (E.G. PL/SQL Developer or TOAD), doing a DESC on the same object fails.

Obviously, I would like it to work there as well, but I am not sure how to proceed.

Brian Received on Fri Jun 29 2001 - 15:53:50 CDT