SQL & PL/SQL

Oracle 10.x

Have not seen any explicit answers to how this can be done from googling, and searching here and on asktom.

The basic SQL construct is as follows:
SELECT * FROM TABLE( function )

Obviously a remote reference requires a db link added, thus:
SELECT * FROM TABLE( function@dblink )

And this is where the error occurs. The TABLE() function is executed locally and it cannot deal with remote objects. If that can be executed remotely then problem solved. right?

This seems to be confirmed as one can create a view on the remote database where this view runs the SELECT. Then one can select from that view remotely. The local SQL is now a select on the above select - and that above select runs remotely.. including the TABLE() function that is also now executed remotely.

So in theory, if the following was valid/support SQL syntax, it would work:
SELECT * FROM TABLE@dblink( function@dblink )

I.e. forcing the TABLE() function to be executed remotely.

The current work around I have is a bit of a hack. I create a namespace (context) on the remote db and a view that is parameter driven using the name-values in this namespace.

From the local db then, calls are made to set name-values in that remote namespace and then a select is done against that remote view.

It works.. but it is a clumsy and potentially error-prone method.

Any ideas or suggestions?


Thanks.