public inbox for [email protected]  

help / color / mirror / Atom feed

From: TIM CHILD <[email protected]>
To: Shweta Rahate <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Calling oracle function from PostgreSQL
Date: Fri, 30 Aug 2024 16:12:10 -0700 (PDT)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
References: <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>

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 <[email protected]> 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
> 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Calling oracle function from PostgreSQL
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox