Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Logon trigger not working over DB-Link?

PyJan 21 2014 — edited Jan 23 2014

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

This post has been answered by Py on Jan 23 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 20 2014
Added on Jan 21 2014
5 comments
898 views