For the past few days, I've literally been stumped at how to allow a trigger to call a remote stored procedure. I created a trigger on a 9i server, calling a procedure on an 11g server. The trigger compilation failed due to an error regarding 'Cannot execute DDL statements on a remote server'.
There were already several triggers set up on the same 9i system calling the same procedure on the 11g server. I was in stumped mode.
Turns out, a public database link had already been created. This is a must in oracle for calling remote databases. The public database was created to connect as the 'aq_admin' user, meaning that the aq_admin user on the remote, 11g server, had to have permissions to execute the procedure.
The aq_admin user had priveleges, in fact it was the owner of the procedure. This stumped me even more.
The aha moment came when I took a look at the synonyms. The other triggers were in the SYSADM schema, and the trigger I was trying to compile was in the PRODADM schema. A synonym to the procedure package was created under the SYSADM schema, but not the PRODADM schema.
After creating the schema, wa-la everything clicked into place.
So here are the instructions (pseudo code) for connecting and executing a remote stored procedure in Oracle.
Create the remote procedure.
Create a (public) database link from the local server to the remote server.
On the remote system, make sure that the user the link is listed under, has privileges to the procedure. (Grant execute on procpackage to user).
Create a synonym under the schema where you need the trigger.
Create synonym "SCHEMA"."SYNONYM" on "SCHEMA"."TABLE"@"NAME_OF_DATABASE_LINK"
Compile the trigger.
And that should do it.
