Re: Calling oracle function from PostgreSQL

 

Another approach is to write  a Postgres UDF (User Defined Function) written in JavaScript using  the PLV8 add-on. https://github.com/plv8/plv8  

You can then write a simple  JavaScript function that connects to the Oracle database and calls your  Oracle function return that result to your Postgres UDF.

 

Most Cloud providers support the installation of PLV8. You'll need ensure firewall and network rules allow outbound and inbound connections between the PostgreSQL server and the Oracle server.  For additional security you should create a special Oracle user with very restricted privileges, to only execute that function, and use that user in the JavaScript  function when you connect to Oracle. You'll also need to take care to not include the Oracle user name and password in your JavaScript code.. You'll have to master the  esbuild JavaScript bundler to include the Oracle JavaScript packages and dependencies.

 

This approach won't be efficient as you be establishing a new Oracle connection every time you call it from PostgreSQL. 

 

-Tim