Postgres Pro
Downloads
Home   >   mailing lists

Thread: Calling oracle function from PostgreSQL

Calling oracle function from PostgreSQL

From
Shweta Rahate
Date:
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

Re: Calling oracle function from PostgreSQL

From
Kashif Zeeshan
Date:
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 <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

Re: Calling oracle function from PostgreSQL

From
Muhammad Ikram
Date:
Hi

Please explore oracle_fdw. 

Regards,
Muhammad Ikram


On Fri, Aug 30, 2024 at 12:09 PM 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


--
Muhammad Ikram

Re: Calling oracle function from PostgreSQL

From
Umair Shahid
Date:

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

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 <mmikram@gmail.com> wrote:
Hi

Please explore oracle_fdw. 

Regards,
Muhammad Ikram


On Fri, Aug 30, 2024 at 12:09 PM 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


--
Muhammad Ikram

Re: Calling oracle function from PostgreSQL

From
Greg Sabino Mullane
Date:
On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <umair.shahid@gmail.com> 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
 

Re: Calling oracle function from PostgreSQL

From
Kashif Zeeshan
Date:


On Fri, Aug 30, 2024 at 12:18 PM Muhammad Ikram <mmikram@gmail.com> 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 <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


--
Muhammad Ikram

Re: Calling oracle function from PostgreSQL

From
TIM CHILD
Date:
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

Re: Calling oracle function from PostgreSQL

From
Laurenz Albe
Date:
On Fri, 2024-08-30 at 11:54 -0400, Greg Sabino Mullane wrote:
> On Fri, Aug 30, 2024 at 11:24 AM Umair Shahid <umair.shahid@gmail.com> 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

Re: Calling oracle function from PostgreSQL

From
Laurenz Albe
Date:
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

Re: Calling oracle function from PostgreSQL

From
Adam Brusselback
Date:
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 <laurenz.albe@cybertec.at> 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