CODEWITHSUNDEEP
oracle-databaseselectplsqluser-defined-functionsdblink
ricardo347
ricardo347

Reputation: 13

How to select * from table () referencing a function from dblink

I'm just starting with PL/SQL. I have a function (table valued), and I can select it running:

SELECT *
FROM TABLE(fn_getsomething());

Ok, but I have to select a similar function (table valued as well) from a remote database @dbfs. How can I write this sentence?

I tried:

SELECT *
FROM TABLE (fn_get_rps_to_rm@dbfs())
//or
SELECT *
FROM TABLE (fn_get_rps_to_rm()@dbfs());

And it returns : 00904. 00000 - "%s: invalid identifier"

I had looked for similar questions, talking about table valued functions and dblink and I didn't find it.

Upvotes: 1

Views: 1435

Answers (1)

Roger Cornejo
Roger Cornejo

Reputation: 1547

I would recommend creating a view on the remote node say:

create or replace view fn_getsomething_view as
SELECT *
FROM TABLE(fn_getsomething());

then query from that view with the link, for example:

select * from fn_getsomething_view@dbfs;

hope this helps.

Upvotes: 3

Related Questions