public inbox for [email protected]help / color / mirror / Atom feed
Calling oracle function from PostgreSQL 10+ messages / 8 participants [nested] [flat]
* Calling oracle function from PostgreSQL @ 2024-08-30 07:08 Shweta Rahate <[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:18 Kashif Zeeshan <[email protected]> parent: Shweta Rahate <[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:18 Muhammad Ikram <[email protected]> parent: Shweta Rahate <[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 15:24 Umair Shahid <[email protected]> parent: Muhammad Ikram <[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 15:54 Greg Sabino Mullane <[email protected]> parent: Umair Shahid <[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 16:48 Kashif Zeeshan <[email protected]> parent: Muhammad Ikram <[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 23:12 TIM CHILD <[email protected]> parent: Shweta Rahate <[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-09-02 11:30 Laurenz Albe <[email protected]> parent: Greg Sabino Mullane <[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-09-02 13:34 Laurenz Albe <[email protected]> parent: Shweta Rahate <[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-09-02 14:50 Adam Brusselback <[email protected]> parent: Laurenz Albe <[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