Database Links and DBMS_SQL

I have installed Oracle 7.3.4 with distributed and replication options on a Windows Nt4.0 (service pack 3) server.

I have created two schemas, one called biomanOP and the other biomanAN. Both have the DBA role assigned and ALL system privileges. While logged in as biomanOP, I created a private database link called ANLYTICAL that uses the ID/password for biomanAN. After logging in as biomanAN, I also created another database link called OPERATIONAL that uses the ID/password for biomanOP. BOTH LINKS test successfully in schema manager. I am also able to query tables through both links.

In biomanOP I create a stored procedure that creates snapshot logs for tables in biomanOP. It also calls a stored procedure in biomanAN (using the database link ANALYTICAL) that creates a snapshot of the table for which the log was created. THE SNAPSHOT IS CREATED USING THE DBMS_SQL PACKAGE. This is my dilemma:
When I log in as biomanAN and execute the stored procedure that creates the snapshot (this is the stored procedure that uses DBMS_SQL), everything is fine. The snapshot is created.

If I log in as biomanOP and run the stored procedure that creates the log and calls the stored procedure in biomanAN via the database link, no errors are reported and no snapshot is created!

Please help!

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 01 1999 - 16:14:33 CST