Oracle Database Discussions

Hi all,

I have a serious question about accessing tables over a database link.

I have three schema:

DATA@SOURCE

INTERFACE@SOURCE

WORK@TARGET

Schema DATA has one table called T1

The INTERFACE schema has select privileges on all tables from DATA. Furthermore schema INTERFACE has a logon trigger to change the "current schema" to DATA:

CREATE OR REPLACE TRIGGER TRG_A_LOGIN_SET_SCHEMA AFTER LOGON
ON INTERFACE.SCHEMA
BEGIN
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = DATA';
END;

The WORK schema has a database link to the INTERFACE schema called INT_DB_LINK.

I am now logged into schema WORK on the TARGET database and I am executing following statement:

select a from T1@INT_DB_LINK

-> it's working

Next I execute

declare
  cursor c is  
  select a
    from T1@INT_DB_LINK
   where rownum<2;
begin
  for r in c loop
    null;
  end loop;
end; 

This is not working. Error message is ORA-000942: table or view does not exist.

But why?

Can anyone help me?

Thanks in advance

Py