Hi,
Here is the scenario -
There is a local transactional application on an Oracle database (say, X) that has a DB link to another remote Data Warehouse Oracle database (say, Y).
For a certain event, the local transactional application needs to have a remote package in database Y executed *in the remote database itself*.
The execution needs to be done in the remote database as all of the local event's impact is on millions of rows in the remote database.
Normally, the remote DB Link user is given GRANT to EXECUTE the remote package, and the package is then available to execute in the local server.
But then it executes locally and that is not what is needed here. The package needs to be executed remotely.
And once the package completes running remotely, can it communicate back to the local application stating that it has completed with its status (success, failure, etc.)
How can the above be achieved?
One thought was to make the local application write one row to a table in the remote DB, and then have an Insert DB trigger on the remote table execute the remote package.
Will above work?
Please suggest any alternative designs too.
Thanks in advance!
Jai