How to access PostgreSQL Functions from Oracle through DBLink?

Question

I'm having two DB server (PostgreSQL, Oracle). I'm able to access the table in PostgreSQL from Oracle through DB-Link. I've use the below query to access the table.

select "Cust_Id", "Mobile_No" from "schema_name"."tablename"@Dblink where "cust_id" = '123456789'; 

As same as I'm trying to access the function in PostgreSQL from Oracle server through DBLink. But I can't access it. I've used below query

select "Cust_id" from "schema_name"."function_name"@DbLink('123456789');

The function will return the mobileno and cust_id in table type.

I'm getting error like.

SQL Command not properly ended

Pls help on this issue. Thnx in advance.

Answer 1

Have you tried creating a view in PostgreSQL and then query the view from Oracle? You need PostgreSQL to present the function like a view since Oracle doesn't really have that function.

I don't currently support PostgreSQL. However, if you create a view in PostgreSQL and test it to verify that it works, you should be able to select from that view in Oracle. It looks like the issue is selecting from a function, not selecting from PostgreSQL.