Calling a stored function (that returns an array of a user-defined type) in oracle across a database link?

What you're trying is the correct syntax as far as I know, but in any case it would not work due to the return type being user-defined, as you suspect.

What you're trying is the correct syntax as far as I know, but in any case it would not work due to the return type being user-defined, as you suspect. Here's an example with a built-in pipelined function. Calling it locally works, of course: SELECT * FROM TABLE(dbms_xplan.

Display_cursor('a',1,'ALL')); Returns: SQL_ID: a, child number: 1 cannot be found Calling it over a database link: SELECT * FROM TABLE(dbms_xplan. Display_cursor@core('a',1,'ALL')); fails with this error: ORA-30626: function/procedure parameters of remote object types are not supported Possibly you are getting the ORA-904 because the link goes to a specific schema that does not have access to the package. But in any case, this won't work, even if you define an identical type with the same name in your local schema, because they're still not the same type from Oracle's point of view.

You can of course query a view remotely, so if there is a well-defined set of possible parameters, you could create one view for each parameter combination and then query that, e.g. : CREATE VIEW display_cursor_a_1_all AS SELECT * FROM TABLE(dbms_xplan. Display_cursor('a',1,'ALL')) ; If the range of possible parameter values is too large, you could create a procedure that creates the needed view dynamically given any set of parameters. Then you have a two-step process every time you want to execute the query: EXECUTE package.

Create_view@remote(parameters) SELECT * FROM created_view@remote; You have to then think about whether multiple sessions might call this in parallel and if so how to prevent them from stepping on each other.

Well, I've confirmed that creating the UDT in the second schema doesn't help (same error). Got any suggested workarounds? – David Marx Aug 30 at 16:29 Best idea I have at the moment is to use views to wrap the function in the remote database.

See edited answer. – Dave Costa Aug 30 at 17:23 Perhaps a better idea is to create a single global temporary table in the remote, invoke a remote function that loads the requested rows into that table, then query it. – Dave Costa Aug 30 at 17:30 The best workaround I've come up with was exactly your above comment, but rebuilding the view is a little more appealing to me than truncating and repopulating a table.In any event, I"ll give you an 'accepted' for your proposed workarounds.

Thanks! – David Marx Aug 30 at 18:14 No problem. Just FYI, a global temporary table wouldn't need to be truncated -- it automatically retains a separate set of rows for each transaction (or session, depending on how it is created) that uses it.

– Dave Costa Aug 30 at 19:11.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.