public inbox for [email protected]
help / color / mirror / Atom feedCalling oracle function from PostgreSQL
10+ messages / 8 participants
[nested] [flat]
* Calling oracle function from PostgreSQL
@ 2024-08-30 07:08 Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Kashif Zeeshan <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Muhammad Ikram <[email protected]>
2024-08-30 23:12 ` Re: Calling oracle function from PostgreSQL TIM CHILD <[email protected]>
2024-09-02 13:34 ` Re: Calling oracle function from PostgreSQL Laurenz Albe <[email protected]>
0 siblings, 4 replies; 10+ messages in thread
From: Shweta Rahate @ 2024-08-30 07:08 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
@ 2024-08-30 07:18 ` Kashif Zeeshan <[email protected]>
3 siblings, 0 replies; 10+ messages in thread
From: Kashif Zeeshan @ 2024-08-30 07:18 UTC (permalink / raw)
To: Shweta Rahate <[email protected]>; +Cc: [email protected]
Hi Shweta
There is no such feature either by Oracle fdw or db links to call Oracle
functions in Postgres as per my knowledge.
Thanks & Regards
Kashif Zeeshan
On Fri, Aug 30, 2024 at 12:09 PM 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
@ 2024-08-30 07:18 ` Muhammad Ikram <[email protected]>
2024-08-30 15:24 ` Re: Calling oracle function from PostgreSQL Umair Shahid <[email protected]>
2024-08-30 16:48 ` Re: Calling oracle function from PostgreSQL Kashif Zeeshan <[email protected]>
3 siblings, 2 replies; 10+ messages in thread
From: Muhammad Ikram @ 2024-08-30 07:18 UTC (permalink / raw)
To: Shweta Rahate <[email protected]>; +Cc: [email protected]
Hi
Please explore oracle_fdw.
Regards,
Muhammad Ikram
On Fri, Aug 30, 2024 at 12:09 PM 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
>
--
Muhammad Ikram
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Muhammad Ikram <[email protected]>
@ 2024-08-30 15:24 ` Umair Shahid <[email protected]>
2024-08-30 15:54 ` Re: Calling oracle function from PostgreSQL Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Umair Shahid @ 2024-08-30 15:24 UTC (permalink / raw)
To: Muhammad Ikram <[email protected]>; +Cc: Shweta Rahate <[email protected]>; [email protected]
To call an Oracle function from a PostgreSQL database, you can use a
Foreign Data Wrapper (FDW) to connect PostgreSQL to Oracle. One of the most
commonly used FDWs for this purpose is oracle_fdw. Here’s a step-by-step
guide to achieve this:
Step 1: Install oracle_fdw
1.
Install Oracle Client Libraries: Ensure that the Oracle client libraries
are installed on your PostgreSQL server. You can download these from the
Oracle website.
2.
Install oracle_fdw: You can install oracle_fdw using your package
manager or by compiling it from source.
For example, on Debian-based systems:
sudo apt-get install postgresql-<version>-oracle-fdw
Or, to compile from source:
git clone https://github.com/laurenz/oracle_fdw.gitcd
oracle_fdwmakesudo make install
Step 2: Configure oracle_fdw
1.
Create the Extension: In your PostgreSQL database, create the oracle_fdw
extension.
CREATE EXTENSION oracle_fdw;
2.
Create a Foreign Server: Define a foreign server that connects to your
Oracle database.
CREATE SERVER oracle_serverFOREIGN DATA WRAPPER oracle_fdwOPTIONS
(dbserver '//oracle_host:1521/oracle_service_name');
3.
Create a User Mapping: Map a PostgreSQL user to an Oracle user.
CREATE USER MAPPING FOR postgresSERVER oracle_serverOPTIONS (user
'oracle_user', password 'oracle_password');
Step 3: Create Foreign Table
1. Create a Foreign Table: Define a foreign table in PostgreSQL that
maps to the Oracle table or view.
CREATE FOREIGN TABLE oracle_table ( column1 datatype, column2
datatype, ...)SERVER oracle_serverOPTIONS (schema 'oracle_schema',
table 'oracle_table');
Step 4: Call Oracle Function
1. Call the Oracle Function: You can now call the Oracle function using
the foreign table. Here’s an example of how you might do this:
SELECT oracle_function(column1, column2)FROM oracle_tableWHERE
some_condition;
Documentation Links
- oracle_fdw GitHub Repository <https://github.com/laurenz/oracle_fdw;
- PostgreSQL Foreign Data Wrapper Documentation
<https://www.postgresql.org/docs/current/ddl-foreign-data.html;
Next Steps
1. Test the Connection: Ensure that the connection between PostgreSQL
and Oracle is working correctly by querying the foreign table.
2. Handle Data Types: Pay attention to data type compatibility between
PostgreSQL and Oracle.
3. Optimize Performance: Consider performance implications and optimize
queries as needed.
IMPORTANT: This is the output from Elethena, the AI chatbot specializing in
PostgreSQL at https://stormatics.tech/.
Thanks!
- Umair
On Fri, Aug 30, 2024 at 12:18 PM Muhammad Ikram <[email protected]> wrote:
> Hi
>
> Please explore oracle_fdw.
>
> Regards,
> Muhammad Ikram
>
>
> On Fri, Aug 30, 2024 at 12:09 PM 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
>>
>
>
> --
> Muhammad Ikram
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Muhammad Ikram <[email protected]>
2024-08-30 15:24 ` Re: Calling oracle function from PostgreSQL Umair Shahid <[email protected]>
@ 2024-08-30 15:54 ` Greg Sabino Mullane <[email protected]>
2024-09-02 11:30 ` Re: Calling oracle function from PostgreSQL Laurenz Albe <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Greg Sabino Mullane @ 2024-08-30 15:54 UTC (permalink / raw)
To: Umair Shahid <[email protected]>; +Cc: Muhammad Ikram <[email protected]>; Shweta Rahate <[email protected]>; [email protected]
On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <[email protected]>
wrote:
> IMPORTANT: This is the output from Elethena, the AI chatbot specializing
> in PostgreSQL at https://stormatics.tech/.
>
Please don't do this. The mailing lists (and planet postgres entries)
should be human-answered. On a related note, the answer is not quite
correct, and the code samples will not work as written due to missing
spaces.
Cheers,
Greg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Muhammad Ikram <[email protected]>
2024-08-30 15:24 ` Re: Calling oracle function from PostgreSQL Umair Shahid <[email protected]>
2024-08-30 15:54 ` Re: Calling oracle function from PostgreSQL Greg Sabino Mullane <[email protected]>
@ 2024-09-02 11:30 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2024-09-02 11:30 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; Umair Shahid <[email protected]>; +Cc: Muhammad Ikram <[email protected]>; Shweta Rahate <[email protected]>; [email protected]
On Fri, 2024-08-30 at 11:54 -0400, Greg Sabino Mullane wrote:
> On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <[email protected]> wrote:
> > IMPORTANT: This is the output from Elethena, the AI chatbot specializing in PostgreSQL at https://stormatics.tech/.
>
> Please don't do this. The mailing lists (and planet postgres entries) should be human-answered.
> On a related note, the answer is not quite correct, and the code samples will not work as written
> due to missing spaces.
Yes, that answer is pretty much utter nonsense.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Re: Calling oracle function from PostgreSQL Muhammad Ikram <[email protected]>
@ 2024-08-30 16:48 ` Kashif Zeeshan <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Kashif Zeeshan @ 2024-08-30 16:48 UTC (permalink / raw)
To: Muhammad Ikram <[email protected]>; +Cc: Shweta Rahate <[email protected]>; [email protected]
On Fri, Aug 30, 2024 at 12:18 PM Muhammad Ikram <[email protected]> wrote:
> Hi
>
> Please explore oracle_fdw.
>
Oracle FDW does not support calling Oracle Functions in Postgres, their
main purpose is accessing table data.
>
> Regards,
> Muhammad Ikram
>
>
> On Fri, Aug 30, 2024 at 12:09 PM 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
>>
>
>
> --
> Muhammad Ikram
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
@ 2024-08-30 23:12 ` TIM CHILD <[email protected]>
3 siblings, 0 replies; 10+ messages in thread
From: TIM CHILD @ 2024-08-30 23:12 UTC (permalink / raw)
To: Shweta Rahate <[email protected]>; [email protected] <[email protected]>
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
@ 2024-09-02 13:34 ` Laurenz Albe <[email protected]>
2024-09-02 14:50 ` Re: Calling oracle function from PostgreSQL Adam Brusselback <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2024-09-02 13:34 UTC (permalink / raw)
To: Shweta Rahate <[email protected]>; [email protected]
On Fri, 2024-08-30 at 12:38 +0530, Shweta Rahate wrote:
> 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.
There is no direct way to do this via oracle_fdw.
There are, however, a couple of hacks to do that; see the following example:
The Oracle function:
CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS
BEGIN
RETURN n * 2;
END;
/
Then I can define an Oracle table with a single row and a trigger on it:
CREATE TABLE call_double(inp NUMBER, outp NUMBER);
INSERT INTO call_double VALUES (1, 1);
COMMIT;
CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW
BEGIN
:NEW.outp := double(:NEW.inp);
END;
/
Now I can define a foreign table as follows:
CREATE FOREIGN TABLE call_double(
inp numeric OPTIONS (key 'true'),
outp numeric)
SERVER oracle OPTIONS (table 'CALL_DOUBLE');
And then the following UPDATE calls the function and returns the result:
UPDATE call_double SET inp = 12 RETURNING outp;
That's ugly, but perhaps it is good enough as a workaround.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Calling oracle function from PostgreSQL
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-09-02 13:34 ` Re: Calling oracle function from PostgreSQL Laurenz Albe <[email protected]>
@ 2024-09-02 14:50 ` Adam Brusselback <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adam Brusselback @ 2024-09-02 14:50 UTC (permalink / raw)
To: [email protected]
Re: That table hack
Oh man is scary as can be (to me). I think I would go with another option
(maybe outside of the database) entirely rather than introducing that into
my codebase.
Onto the general need:
I've definitely had the need for foreign function calls between my (both
Postgres) databases (e.g. dwh server calling a function to get some info
from oltp server), and I had to resort to dblink for that. Would have been
very nice if the FDW interface had support for functions / stored
procedures as first class citizens as long as the fdw implementation (and
other endpoint) support functions / stored procedures.
Once could dream.
-Adam
On Mon, Sep 2, 2024 at 9:34 AM Laurenz Albe <[email protected]>
wrote:
> On Fri, 2024-08-30 at 12:38 +0530, Shweta Rahate wrote:
> > 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.
>
> There is no direct way to do this via oracle_fdw.
>
> There are, however, a couple of hacks to do that; see the following
> example:
>
> The Oracle function:
>
> CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS
> BEGIN
> RETURN n * 2;
> END;
> /
>
> Then I can define an Oracle table with a single row and a trigger on it:
>
> CREATE TABLE call_double(inp NUMBER, outp NUMBER);
>
> INSERT INTO call_double VALUES (1, 1);
>
> COMMIT;
>
> CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW
> BEGIN
> :NEW.outp := double(:NEW.inp);
> END;
> /
>
> Now I can define a foreign table as follows:
>
> CREATE FOREIGN TABLE call_double(
> inp numeric OPTIONS (key 'true'),
> outp numeric)
> SERVER oracle OPTIONS (table 'CALL_DOUBLE');
>
> And then the following UPDATE calls the function and returns the result:
>
> UPDATE call_double SET inp = 12 RETURNING outp;
>
> That's ugly, but perhaps it is good enough as a workaround.
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-09-02 14:50 UTC | newest]
Thread overview: 10+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-30 07:08 Calling oracle function from PostgreSQL Shweta Rahate <[email protected]>
2024-08-30 07:18 ` Kashif Zeeshan <[email protected]>
2024-08-30 07:18 ` Muhammad Ikram <[email protected]>
2024-08-30 15:24 ` Umair Shahid <[email protected]>
2024-08-30 15:54 ` Greg Sabino Mullane <[email protected]>
2024-09-02 11:30 ` Laurenz Albe <[email protected]>
2024-08-30 16:48 ` Kashif Zeeshan <[email protected]>
2024-08-30 23:12 ` TIM CHILD <[email protected]>
2024-09-02 13:34 ` Laurenz Albe <[email protected]>
2024-09-02 14:50 ` Adam Brusselback <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox