Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skAoV-008N5a-SD for pgsql-novice@arkaria.postgresql.org; Fri, 30 Aug 2024 23:13:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1skAnU-00Cr6L-Bb for pgsql-novice@arkaria.postgresql.org; Fri, 30 Aug 2024 23:12:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skAnT-00Cr2c-BT for pgsql-novice@lists.postgresql.org; Fri, 30 Aug 2024 23:12:52 +0000 Received: from resqmta-h2p-567408.sys.comcast.net ([2001:558:fd02:2446::8]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skAnF-002FOZ-VP for pgsql-novice@lists.postgresql.org; Fri, 30 Aug 2024 23:12:43 +0000 Received: from resomta-h2p-555061.sys.comcast.net ([96.102.179.200]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 256/256 bits) (Client did not present a certificate) by resqmta-h2p-567408.sys.comcast.net with ESMTPS id k9RVshdoc9bHAkAnBsPCUq; Fri, 30 Aug 2024 23:12:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1725059554; bh=qZz9QCTb7d7/Xp28ec86bo511Hj+ztwcsvOpr042wCw=; h=Received:Received:Date:From:To:Message-ID:Subject:MIME-Version: Content-Type:Xfinity-Spam-Result; b=ZNpCi+7HtN89j9SIQs+Jnk/20MTlfqm4K7iUFHRTQCuhZBnMD6cTdUjTqhQiCkz2F zDepYhIpfVmFkqWgEw0u1Bwm6kcw6fYE5ZG71OTIsZmCzEWQzlmgt6mboW8D8zbdmZ Yg+TlavoFdasJaejsIeaFtp59ZFsQjfVyR7h6WIVgzy4MXtlj9QZn804N7W7f7STUL NNsggpGgVuvJ6lbKwnNtbPUnWdsdJW5rhO7Hhw377hpesFupGqBVaeUdXHPehd77cN gUiWb8AfXhlTUginRUQtXK7YsHZuqEtQC0gmRBEuwv1p0Gp1NcfOZbEYrm3/urPxn4 y2pmekiWIz+vA== Received: from oxapp-hoa-56o.email.comcast.net ([96.116.225.159]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resomta-h2p-555061.sys.comcast.net with ESMTPS id kAmosi8nguj44kAmosxq3o; Fri, 30 Aug 2024 23:12:11 +0000 Date: Fri, 30 Aug 2024 16:12:10 -0700 (PDT) From: TIM CHILD To: Shweta Rahate , "pgsql-novice@lists.postgresql.org" Message-ID: <879742875.262037.1725059530818@connect.xfinity.com> In-Reply-To: References: Subject: Re: Calling oracle function from PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_262036_198141541.1725059530787" X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.6-Rev59 X-Originating-IP: ::ffff:76.102.200.136 X-Originating-Client: open-xchange-appsuite X-CMAE-Envelope: MS4xfJ1b7ppbIitJ6svymzyJI0tL9Ii6D1W8+82Y0vgVzHdjFEVEp3iWR5kTWWZPIsyQ7bQax5UxttQPxuE9xCAfg7eeRiuY2DVA/hBh6f3P/HaQNjZGTK+c h76bvutZmyDSq+RiTdvkK4L5KWoy73PCRcchYpU9MgLJ7Ok5NRVCSKWRvKr9WUvgiXtbD5Pbce9NirQZeRBPba7XvcDIbiscReXz6WHdHnD4Te2ggS9ANPCM E1gnsArG8RCb2e+UmygDMGC2J9M5OgKNpvlPsRfmXT4= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_262036_198141541.1725059530787 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 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 > ------=_Part_262036_198141541.1725059530787 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
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
------=_Part_262036_198141541.1725059530787--