Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Triggers and Database Links 1

Status
Not open for further replies.

Kenrae

Programmer
Feb 7, 2001
174
ES
Hello everybody.

I have a problem with a trigger which doesn't compile. The error is "PLS-00201: identifier 'USERS_LINK' must be declared"
USERS_LINK is a synonym for a table in another schema, accesed using a database link. I've tried to substitute it for the table@link directly, but I get the same error message.
Here is the code:


CREATE OR REPLACE TRIGGER aibak_fk
before insert or update on aibak
for each row

DECLARE
user varchar2(100);
fk EXCEPTION;
PRAGMA EXCEPTION_INIT(fk,-2291);
BEGIN
select nt_user into user from users_link where
nt_user = :new.owner_id;
select nt_user into user from users_link where
nt_user = :new.requester_id;

EXCEPTION WHEN NO_DATA_FOUND THEN
raise fk;
END;
/


I can execute the select statements separately, and it works fine.
Any ideas?

Thanks
 
When you run the commands separately, are you logged in with the same account as when the trigger is run? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
To expand on Terry's question - is this a private or public database link?
 
Yes, it's the same account. And the db link is private, and it's owned by the account which executes the trigger.
 
You might try recreating the db link as a public link and see if that changes the results.
 
I haven't tried it, but it's impossible. This db link can't be public.
And now, I've tried to do it on a procedure and I have the same compilation error!
I've accesed synonyms with pl/sql a thousand times, but now it seems I'm doing something wrong, unknown to me. I'm really confused.
 
At last, I've found a solution. Sometimes we have to look back to what we learned some years ago. Now I'm only using privileges instead of db links, I don't know why I haven't done it before...
 
Can you tell me about your solution? I have this problem too. My remote Oracle8 procedure was called through db_link from Oracle 7 and everything worked normally. Now we're moving from 7 to 8i and can not call remote procedure neither from another stored proc nor from sql*plus. We still can call some remote procedures, but this one issues rollback (the only difference I've found). So could you explain please how to use privileges instead of db_links?
 
Well, actually, it was an error. I shouldn't have used a db link at first, because both schemas, or users, where on the same machine (service). That's why I ended using grants.
Reading some Oracle information, I encountered that you can only access a public db link in a trigger or a procedure. But you should be able to call your procedure from sql*plus.

What kind of error do you have?
 
I have PLS-00907 : cannot load library unit string (referenced by string).

 
Mmm, that's not the error I had.
I suppose you've tried to remake the db link...
Well, you could try to create a synonym for that library, or make the link as a public link. I'm sorry but I don't know what else might be :-(
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top