Re: Calling oracle function from PostgreSQL

Shweta,

Another approach is to write a Postgres UDF (User Defined Function) written in JavaScript using the PLV8 add-on. https://github.com/plv8/plv8 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 https://esbuild.github.io/ 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

> On 08/30/2024 12:08 AM PDT Shweta Rahate <rahateshweta20(at)gmail(dot)com> wrote:
>
>
> Hi All,
>
> In my application there is a requirement to call the oracle function from PostgreSQL db.
>
> The oracle function should take the input from Postgres db and returns the output.
> Please suggest a way to achieve this.
>
>
>
> Regards,
> Shweta
>