4

Normally, I call my function like so:

SELECT * 
FROM TABLE(
  package_name.function(parameters)
)

I'm trying to call this function across a database link. My intuition is that the following is the correct syntax, but I haven't gotten it to work:

SELECT * 
FROM TABLE(
  package_name.function@DBLINK(parameters)
)

> ORA-00904: "PACKAGE_NAME"."FUNCTION": invalid identifier

I've tried moving around the database link to no effect. I've tried putting it after the parameter list, after the last parenthesis, after the package name...I've also tried all of the above permutations including the schema name before the package name. I'm running out of ideas.

This is oracle 10g. I'm suspicious that the issue may be that the return type of the function is not defined in the schema in which I'm calling it, but I feel like I should be getting a different error if that were the case.

Thanks for your help!

David Marx
  • 8,172
  • 3
  • 45
  • 66

1 Answers1

4

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • 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 '11 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 '11 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 '11 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 '11 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 '11 at 19:11