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 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
On 08/30/2024 12:08 AM PDT Shweta Rahate <rahateshweta20@gmail.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
